SUBSTITUTE function" Excel, a method to convert numbers in addresses to Chinese numerals in one shot.

For example, when printing addresses on envelopes using the address book of a New Year's card or a list of addresses in a customer list,Arithmetic numbers such as "1, 2, 3" for horizontal envelopes(Arabic numerals)Although it is denoted by,Chinese numerals such as "1, 2, 3" for vertical envelopesThe name must be indicated in the following format

How to replace numbers:

• How to use "Format Cells"
• Direct replacement method
• How to replace by "SUBSTITUTE function

There are multiple ways to do this, including

However, when it comes to numbers contained in addresses, "Format Cells" cannot be used.
The direct replacement method is also impractical because of the risk of irreversibility if the replacement fails during the process, and the need to replace the numbers again in the same way if you want to go back to the original numbers.

In this article, we will focus on how to use the SUBSTITUTE function to make substitutions.

I'll go ahead and answer the question.A1The string containing the numbers entered in the "+" cell isAll converted to Chinese numeralsTo do so, use the "SUBSTITUTE function" as follows.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(JIS(A1), "1", "1"), "2", "2"), "3", "3"), "4", "4"), "5", "5"), "6", "6"), "7", "7"), "8", "8"), "9", "9"), "0", "0")

In this article, we will explain in detail how to describe this function.

Also, for older versions of Excel, such as Excel 2003 or earlier, the above functions do not replace well.
We will also introduce how to deal with such 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. ↓↓↓↓

Easy with Excel and Word! Mastering the art of address printing that anyone can do!

Have you ever had to worry about writing addresses for New Year's cards and summer greeting cards? Writing dozens of cards by hand is a lot of work, but buying specialized software is also a bit expensive.... Everyone has experienced this dilemma at least once. But the truth is, your computer...

rebizru.com

Basic usage of the SUBSTITUTE function

The rules for describing the SUBSTITUTE function are as follows

=SUBSTITUTE(string, search string, replace string, [replace target])

For example, suppose that a string containing the mathematical numeral "1" is entered in cell "A1" as shown in the figure below.

Then enter the following in the "A2" cell

=SUBSTITUTE(A11,1, "i")

Pressing the "enter" key in this state allowed the "1" arithmetic number to be replaced by the "1" Chinese numeral, as shown in the figure below.

Substituting all numbers with the SUBSTITUTE function

However, in this state, only "1" is substituted, so when there are "1234567890" rats, the number of "1234567890" rats will be replaced by "1234567890" rats.

The display will show a mixture of arithmetic and Chinese numerals, for example, "one 234567890 rats.

To solve this, we need to replace 1 through 9 and 0 as well. So here is the formula with all numbers replaced.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A11", "1"), "2", "2"), "3", "3"), "4", "4"), "5", "5"), "6", "6"), "7", "7"), "8", "8"), "9", "9"), "0", "0")

If you put this formula in the "A2" cell

Thus all numbers could be replaced with numbers.

When double-byte numbers are mixed in

The method just described is limited to cases where the numbers are one-byte numbers.

In the above example, if only the middle "5" is a full-width number, only full-width numbers cannot be replaced as shown in the figure below.

To solve this problem, use the "ASC function". The "ASC function" converts the value of a specified cell to half-width characters. An example of using the "ASC" function is shown below.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(ASC(A1)1", "1"), "2", "2"), "3", "3"), "4", "4"), "5", "5"), "6", "6"), "7", "7"), "8", "8"), "9", "9"), "0", "0")

Now all numbers can be replaced, even if they are mixed with double-byte numbers.

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(JIS(A1)1", "1"), "2", "2"), "3", "3"), "4", "4"), "5", "5"), "6", "6"), "7", "7"), "8", "8"), "9", "9"), "0", "0")

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(A11, "1"),2, "2"),3, "3"),4, "4"),5, "5")

2nd step

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A26", "6"), "7", "7"), "8", "8"), "9", "9"), "0", "0"), "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.