# Numbers to Words

One of the most asked for Excel features is the ability to turn a number into words. Because many people need this ability, and Excel does not currently possess such a feature, the old saying “Necessity is the mother of invention” took over.  One such VERY inventive person is PETE M.!!!!! Pete M. is a commercial manager and consultant, and Pete needed this ability.  Pete took it upon himself to create such a feature.

But Pete had some requirements for the feature:

1. The feature would not use any VBA code.
2. The feature would not use complicated array formulas (CTRL-Shift-Enter formulas).
3. The feature would not use any helper columns in the spreadsheet.
4. The feature can accommodate up to 2-decimal place accuracy.
5. The feature will work with numbers between 1 and 999,999,999.

Everything had to be contained within a single cell’s formula. ## Behold… Pete’s creation!!!!!!!!!! ## DON’T RUN AWAY JUST YET!

Don’t worry if this looks a bit intimidating; you don’t need to understand this to use it.  We only need to know how to integrate it into our spreadsheets.

SCROLL DOWN TO DOWNLOAD THE WORKBOOK & CHECK OUT THE POWER QUERY SOLUTION TO THIS (sent to us by our wonderful community)

# Let’s look at some examples    This formula is quite complex, having to account for such instances of where it sees values such as “Eleven” (as opposed to “One and One”), “Twenty”, “Thirty”, and weather to say things like “Hundred” versus “Hundred and” something.

The combination of numbers to words can be quite daunting.

# and Decimal Usage

Suppose you need to update this formula to work with US dollars and you don’t require the decimal places.

If you look at the very end of the formula, you will see the portion that is responsible for the decimal places.

`&RIGHT(TEXT(B3,"000000000.00"),2)&"/100"` If you do not require decimal places to be displayed, erase this portion of the formula.

For the currency type, change the portion labeled “Euro” to “USD” or “CAD” or whatever currency designation you wish. Let’s see how that appears after our little formula tweak. # How to apply the formula to many cells

Suppose you have a spreadsheet and you wish to enter a number in cell B4 and have the formula answer appear in the cell directly to the right in cell C4.

We need to make sure that none of the cell references change when copying the formula to a new location.

1. Place your cursor on the cell holding the original formula and press F2 to enable edit mode (or click in the Formula Bar).
2. Select the entire formula by pressing CTRL-A (or manually highlighting the formula. CTRL-A is faster and more accurate.)
3. Press CTRL-C to copy the formula.
4. Press the ESC (Escape) key to back out of edit mode.
5. Switch to the location and cell you wish to use this formula and press F2 to enable edit mode.
6. Press CTRL-V to paste the formula into the new cell.

We’re not quite there yet because all the cell references are pointing to cell B3.  If your data entry cell is in fact cell B3, you are ready to go.  If not, we need to update the cell references to point to the proper data entry location.

Because our original formula was looking at cell B3 for the number and we wish to enter our number in cell B4, we will now perform the following steps to adjust our cell references:

1. Place your cursor on the cell holding the pasted formula and press F2 to enable edit mode (or click in the Formula Bar).
2. Remove the equals sign (=) from the beginning of the formula.
3. Press ENTER. We now have just a massive amount of text in the cell.
4. Press CTRL-H to open the Find/Replace dialog box.
5. In the “Find what:” field, enter “B3” (no double-quotes).
6. In the “Replace with:” field, enter “B4” (no double-quotes).
7. Press the “Replace All” button. Restore the equals sign to the beginning of the formula from where we removed it earlier in step 2.

# Running it through some tests

To test the formula quickly and over multiple iterations, select cell B4 and enter the following formula.

`=RANDBETWEEN(1,2000000)`

This formula will generate a random number between 1 and 2-million each time the sheet recalculates. You can force a recalculation by pressing the F9 key on the keyboard or pressing Formulas (tab) -> Calculation (group) -> Calculate Now. To see several examples simultaneously, select cells B4 and C4 and pull the Fill Series handle down several rows.  This will generate several random numbers and their text counterparts. # Bonus Modification

If you decide to not display the fractional side of the number, the above modification simply removes the fractional values without any rounding operation.

If you would like to round the input to the nearest whole number, implement the following steps:

1. Place your cursor on the cell holding the pasted formula and press F2 to enable edit mode (or click in the Formula Bar).
2. Remove the equals sign (=) from the beginning of the formula.
3. Press ENTER. We now have just a massive amount of text in the cell.
4. Press CTRL-H to open the Find/Replace dialog box.
5. In the “Find what:” field, enter “B3” (no double-quotes).
6. In the “Replace with:” field, enter “ROUND(B3,0)” (no double-quotes).
7. Press the “Replace All” button. Restore the equals sign to the beginning of the formula from where we removed it earlier in step 2.

Don’t forget to change the B3 reference to whichever cell you are performing the data entry.

# Are you curious as to how all this works?

This formula relies on the use of four key functions:

• LEFT
• MID
• TEXT
• CHOOSE

It’s not necessary to dissect every component of the formula, we only need to decipher the first bit highlighted below. ```CHOOSE(LEFT(TEXT(B3),"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--LEFT(TEXT(B3),"000000000.00"))=0,,IF(AND(--MID(TEXT(B3),"000000000.00"),2,1)=0,--MID(TEXT(B3),"000000000.00"),3,1)=0)," Hundred"," Hundred and "))```

Once we have this portion figured out we’ll be able to figure out the remainder of the formula since it is very much a repeated operation.

## The LEFT Function

The purpose of the LEFT function is to extract a specific number of characters from the text starting from the left side.  The structure of the LEFT function is as follows.

`=LEFT(text, [num_chars])`

The parameter “text” refers to the cell holding the input, while “[num_chars]” indicates the number of characters to extract.  Because the “[num_chars]” parameter is optional, skipping this parameter will result in a default extraction of 1 character.

## The MID Function

The purpose of the MID function is to extract a specific number of characters from the text starting from a specific character position (counting from the left side).  The structure of the MID function is as follows.

`=MID(text, start_num, num_chars)`

The parameter “text” refers to the cell holding the input; the parameter “start_num” indicates the position to begin text extraction (counting from the left side), while “num_chars” indicates the number of characters to extract.

## The TEXT Function

The purpose of the TEXT function is to represent a cell’s information with specific formatting.  The structure of the TEXT function is as follows.

`=TEXT(value, format_text)`

Normally, formatting is applied to a cell through traditional cell formatting controls. The TEXT function applies formatting (fancy word alert) formulaically. This way, we can change the formatting of information dynamically based on the need of the moment.

EXAMPLE:  Suppose you have a calculation that needs to reflect U.S. Dollars or Euros depending on a country selection.  The formula could look something like the following:

`=IF(Country=”USA”, TEXT(Sale, ”\$#,##0.00”), TEXT(Sale, “€#,##0.00”))`

If the user selects USA (cell A2) , they get the total sales represented by a “\$” sign (cell C4) .  If the user selects any other country, the total sales is represented by a “” symbol. This function relies on a cell named “Country” (cell A2) where a user may select a country from a Data Validation dropdown list, and a cell named “Sale” (cell A9) that may hold something like a SUM function that adds all the sales together.

In our specific example, the function…

`=TEXT(B3,”000000000.00”)`

…pads the input number with leading zeroes.  By counting the leading zeroes, the formula will be able to determine if the number is in the hundreds, thousands, or millions.

If a user inputs a number, such as 123456.78, the TEXT function will interpret the number as 000123456.78.  The three leading zeroes indicates a number in the thousands.

## The CHOOSE Function

The CHOOSE function is used to simplify multiple nested IF functions that are examining the same data.  CHOOSE is especially useful when working with indexes.

The purpose of the CHOOSE function is to select a value from a built-in list of values based on a supplied number.

`=CHOOSE(index_num, value1, [value2], …)`

If we were to extract the first (left-most) number from the input number and give it to the following CHOOSE function, what do you think we would get back from the CHOOSE function?

`=CHOOSE(LEFT(B3), “One”, “Two”, “Three”, “Four”, “Five”, “Six”, “Seven”, “Eight”, “Nine”)`

We would see the word version of the extracted number.  “2” would yield “Two”, “5” would yield “Five”, etc…

# Putting it All Together

In the formula, we are combining all the functions into a single formula, each function performing their respective parts to accomplish the mission.

```CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--LEFT(TEXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID(TEXT(B3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))```

First we use the TEXT function to turn the number into a “000000000.00” format.

`TEXT(B3,"000000000.00")`

Then, we extract the left-most character form the number.

`LEFT(TEXT(B3,"000000000.00"))`

This will allow us to determine if the returned number is a zero or any other value.  This will indicate weather or not our number is in the “millions” range.

Next, we CHOOSE how to represent the extracted value.

`CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")`

We then check to see if the value is a zero.

```CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--LEFT(TEXT(B3,"000000000.00"))=0,,```

If the value is a zero, then we are not in the millions so we will display nothing.

If the next two digits are zero, then we will display “Hundred”; otherwise, we will display “Hundred and”.

```CHOOSE(LEFT(TEXT(B3,"000000000.00"))+1,,"One","Two","Three","Four","Five","Six","Seven","Eight","Nine")

&IF(--LEFT(TEXT(B3,"000000000.00"))=0,,IF(AND(--MID(TEXT(B3,"000000000.00"),2,1)=0,--MID(TEXT(B3,"000000000.00"),3,1)=0)," Hundred"," Hundred and "))```

# That’s It!

This formula doesn’t rely on any

• VBA
• CTRL-Shift-Enter Array Formulas, or
• Helper Cells

Thank you, Pete for sharing your formula with the Excel community.  I’m certain many will find your solution both creative and highly useful.

## Practice Workbook

Feel free to Download the Workbook HERE. #### Also many thanks to Abdul Rahman Mohammed for providing the Qatari Riyals (QAR), Indian Rupees (INR) & Bahraini Dinars (BHD) in both absolute and rounded figures.

The reworked versions are available in the downloadable Workbook.

## Power Query Solution!

A Power Query solution of transforming numbers to works was sent to us by Kunle SOPEJU.

Check out his Power Query solution HERE. # Learn when you like, where you like.

## Check out my bestselling Excel Courses

Learn anytime that fits your schedule.

1. Thanh May 16, 2019 at 1:46 pm - Reply

I have another formula and it also works
https://gist.github.com/ndthanh/3644114f4619a4a25e8a9148fc92ca27

• Tad Stevens May 17, 2019 at 1:13 am - Reply

That works as well, and it only increases the formula’s length by a mere 2,000 characters.

2. Derek Fingleson May 16, 2019 at 2:42 pm - Reply

Absolutely Amazing Formulae !!!…..
And not only are you Beautiful.. But you a very knowledgeable girl and a great teacher Too….

3. Bashe May 18, 2019 at 6:37 am - Reply

Many thanks to Mr. Pete M. and Tutor, this formula will help me alot.

4. Manned May 19, 2019 at 1:07 am - Reply

Thank you very much

5. SK Rao May 20, 2019 at 5:00 am - Reply

Please advise how I can change it to three decimal places.

6. Luksimple LA. May 27, 2019 at 12:36 pm - Reply

Wow! This is really amazing. Thanks a lots to Mr Pete. You did a great Job. To you Leila Ghairani, you are my mentor. Am always proud of you, most especially with your tutoring style & simplified techniques. May you please assist me with your Email?

• RK July 10, 2019 at 4:00 pm - Reply

99,99,999.99 ninety nine lakhs ninety nine thousand nine hundred and ninety nine rupees and ninety nine paisa only.

7. Mohammad Almomani May 29, 2019 at 6:27 pm - Reply

Thank you so much.. It’s really crazy and creative formula

8. Jenni Smith May 29, 2019 at 10:57 pm - Reply

Gorgeous formula. Thank you so much for sharing it.

I noticed that the VersionUSA in the workbook needs a minor tweak. When I was entering gibberish numbers for fun, the final digit was smashed up next to the word and so I added a space inside the quotation marks before the and at the very end of the formula to achieve the gap.

9. B.H. May 30, 2019 at 2:05 pm - Reply

WOW! This is very interesting.

I would like to see a modification that would allow the user to enter a number like 1234.567 and see the output as one thousand two hundred thirty four and five hundred sixty seven thousandths or 0.06 and get six hundredths.

10. MAHER June 11, 2019 at 7:34 am - Reply

HELLO , WORK VERY WELL DONE , THANK YOU PETE FOR THE EFFORT AND THANK YOU FOR THE EXPLANATION.
I VALUE YOUR ROLE AS A TEACHER VERY MUCH. THANK YOU FOR EVERY THINK.

11. Suresh karne June 14, 2019 at 2:37 pm - Reply

Great work mam.

12. SURESH KARNE June 15, 2019 at 4:32 am - Reply

Mam when I entered “120000” it shows “One Hundred and Twenty Thousand Rupees & 00/100” but it should show “One Lakh Twenty Thousand” so please explain me.

• Onesurya Kumar August 12, 2019 at 8:42 pm - Reply

You can find a newest Formula, presented by JMT Engineering Knowledge and just paste the formula in a cell and get a surprised results its really converted Word according Indian currency like Crores , lakh, Thousand ……plz find bellow and subscribe our Chanel
Please Subscribe and press the bell icon then JMT Engineering team can bring videos without any hesitate

https://youtu.be/DNxFCfUAEvI

13. Samuel Kodjoe Deladem June 29, 2019 at 6:41 pm - Reply

Y.ve got so much LIGHT!!!

14. Javan June 30, 2019 at 6:52 am - Reply

Hi Leila, thanks for sharing this great master piece from Pete (great job indeed!).

But can you just clarify what role plays +1 in “LEFT(TEXT(B3,”000000000.00″))+1”?

Thanks

15. Javan June 30, 2019 at 7:42 am - Reply

one more question is why does Pete in Choose formula after index he start with empty space ,,”One”, instead of e.g. “One”?

16. Javan June 30, 2019 at 7:47 am - Reply

I believe my second question should somehow answer to the first one, so please feel free to combine them if you find it appropriate.

17. Xuhaib July 10, 2019 at 6:44 am - Reply

Superb……… You are a beauty with intelligence.