Sometimes, you may need to convert numbers to words in Excel, such as writing a check or a receipt. For example, you may want to convert 123 to “one hundred twenty-three”. However, Excel does not have a built-in function to do this task. In this article, we will show you how to use the SUBSTITUTE function in Excel to convert numbers to words in a simple and flexible way.
What is the SUBSTITUTE Function?
The SUBSTITUTE function is a text function that replaces text in a given string by matching. The syntax of the SUBSTITUTE function is:
=SUBSTITUTE(text, old_text, new_text, [instance_num])
The arguments of the SUBSTITUTE function are:
text: The text or the reference to a cell containing text for which you want to substitute characters.old_text: The text you want to replace.new_text: The text you want to replaceold_textwith.instance_num: Optional. Specifies which occurrence ofold_textyou want to replace withnew_text. If you specifyinstance_num, only that instance ofold_textis replaced. Otherwise, every occurrence ofold_textintextis changed tonew_text.
The SUBSTITUTE function is case-sensitive and does not support wildcards. For example, =SUBSTITUTE("apple","p","b") returns “abble”, but =SUBSTITUTE("apple","P","b") returns “apple”.
How to Use the SUBSTITUTE Function
To use the SUBSTITUTE function to convert numbers to words, we need to follow these steps:
- Create a table that contains the number and the corresponding word for each digit from 0 to 9. For example, in cells A1:B10, enter the following data:
| Number | Word |
|---|---|
| 0 | zero |
| 1 | one |
| 2 | two |
| 3 | three |
| 4 | four |
| 5 | five |
| 6 | six |
| 7 | seven |
| 8 | eight |
| 9 | nine |
- Enter the number you want to convert to words in a cell, such as C1. For example, enter 123 in cell C1.
- In another cell, such as D1, enter the following formula:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,0,VLOOKUP(0,$A$1:$B$10,2,0)),1,VLOOKUP(1,$A$1:$B$10,2,0)),2,VLOOKUP(2,$A$1:$B$10,2,0)),3,VLOOKUP(3,$A$1:$B$10,2,0)),4,VLOOKUP(4,$A$1:$B$10,2,0)),5,VLOOKUP(5,$A$1:$B$10,2,0)),6,VLOOKUP(6,$A$1:$B$10,2,0)),7,VLOOKUP(7,$A$1:$B$10,2,0)),8,VLOOKUP(8,$A$1:$B$10,2,0)),9,VLOOKUP(9,$A$1:$B$10,2,0))
This formula uses 10 nested SUBSTITUTE functions to replace each digit in the number with the corresponding word from the table. It also uses the VLOOKUP function to look up the word from the table based on the digit. The result is “one two three” in cell D1.
- To add spaces between the words, you can use the TRIM function to remove any extra spaces. For example, enter the following formula in cell E1:
=TRIM(D1)
The result is “one two three” in cell E1.
- To add hyphens between the words, you can use another SUBSTITUTE function to replace the spaces with hyphens. For example, enter the following formula in cell F1:
=SUBSTITUTE(E1," ","-")
The result is “one-two-three” in cell F1.
- To add the word “and” before the last word, you can use the LEFT, RIGHT, and LEN functions to extract the first and last words, and concatenate them with the word “and”. For example, enter the following formula in cell G1:
=LEFT(F1,LEN(F1)-FIND("-",F1,1))&" and "&RIGHT(F1,LEN(F1)-FIND("-",F1,LEN(F1)-FIND("-",F1,1)))
This formula uses the FIND function to locate the position of the last hyphen in the string, and then uses the LEFT and RIGHT functions to extract the first and last words. It also uses the LEN function to calculate the length of the string. The result is “one-two and three” in cell G1.
