When printing addresses on envelopes in Japan, the number format depends on the envelope orientation. For horizontal envelopes, addresses use Arabic numerals (1, 2, 3), while vertical envelopes require Kanji numerals (一, 二, 三). This becomes particularly important when preparing New Year's cards or printing customer address lists.
There are several methods to convert numbers to Kanji:
- Using "Format Cells"
- Direct replacement method
- Using the SUBSTITUTE function
However, when dealing with numbers in addresses, "Format Cells" cannot be used effectively. The direct replacement method is also impractical because changes are difficult to reverse if something goes wrong, and you would need to manually convert everything back to restore the original numbers.
This article focuses on using the SUBSTITUTE function, which provides a reliable and reversible solution for number conversion.
To convert all numbers in a text string to Kanji numerals, you can use the following SUBSTITUTE function formula. For example, if you have a string containing numbers in cell A1, this formula will convert all Arabic numerals to their Kanji equivalents:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A1),"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八"),"９","九"),"０","〇")This article will explain how to use this function step by step.
Note: For older versions of Excel (2003 and earlier), this nested function approach may not work properly due to function nesting limitations. We'll also cover how to handle these cases.
Here is an article on how to easily print an address book or list of addresses created in Excel using Word. Please refer to it as well. ↓↓↓↓
Basic usage of the SUBSTITUTE function
The SUBSTITUTE function uses the following syntax:
=SUBSTITUTE(text, old_text, new_text, [instance_num]) Where: - **text**: The original text containing the characters you want to replace - **old_text**: The characters you want to find and replace - **new_text**: The replacement characters - **instance_num** (optional): Specifies which occurrence to replace
For example, suppose cell A1 contains text with the Arabic numeral "1" as shown in the figure below.
To convert this to a Kanji numeral, enter the following formula in cell A2:
=SUBSTITUTE(A1,1,"一")A1 
When you press Enter, the Arabic numeral "1" will be replaced with the Kanji numeral "一" as shown in the figure below.
Substituting all numbers with the SUBSTITUTE function
However, this formula only replaces the digit "1". If your text contains multiple different numbers, like "1234567890 items", only the "1" will be converted, resulting in "一234567890 items".
This creates a mixture of Kanji and Arabic numerals, which is not the desired result.
To solve this problem, we need to replace all digits (0-9). Here is the complete formula that replaces all numbers:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")When you enter this formula in cell A2, you'll get the following result:
Now all Arabic numerals have been successfully converted to their corresponding Kanji numerals.
When Full-Width Numbers Are Mixed In
The method described above only works when all numbers are half-width (standard) characters.
If your text contains full-width numbers (like ５), these will not be converted by the previous formula, as shown in the figure below.
To solve this problem, use the ASC function. The ASC function converts all full-width characters in a cell to half-width characters. Here's how to use it:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A1),"1","一"),"2","二"),"3","三"),"4","四"),"5","五"),"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")Now all numbers can be replaced, even when full-width and half-width numbers are mixed in the text.
However, if you look closelyMake it half-width to the "rat" part.The reason for this is that the "ASC function" converts all characters with half-width characters to half-width characters. This is because the "ASC function" converts all characters with half-width characters to half-width characters.
If you wish to keep katakana and alphabets in full-width characters, you can do the opposite and display them in full-width characters by aligning them to full-width characters. In this case, the function changes as follows. This is the same function introduced at the beginning of this article.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A1),"１","一"),"２","二"),"３","三"),"４","四"),"５","五"),"６","六"),"７","七"),"８","八"),"９","九"),"０","〇")If you enter this in the "A2" cell, both half-width and full-width numbersAll could be converted to Chinese numerals.
In case of error
Most people who use Excel now will be fine, but for versions up to Excel 2003, the above method is the only way to go.Only 7 functions can be stackedSo, it is an error.
In that case, 1 to 5 and 6 to 0.Separate functions in two steps.It must be stated.
Enter the first step in cell "A2" and the second step in cell "A3".
1st step
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,1,"一"),2,"二"),3,"三"),4,"四"),5,"五")2nd step
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"6","六"),"7","七"),"8","八"),"9","九"),"0","〇")Then, as shown in the figure below, the "A3" cell was correctly displayed.
Conclusion
The SUBSTITUTE function can be used to substitute Chinese numerals without changing the original data.
Even if an address contains numbers, this function can be used to replace them with Chinese numerals, as shown in the figure below.
Please use this method to improve the efficiency of your operations.
English 
日本語
Comment