Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Converting Numbers Into Words.

Converting Numbers Into Words

by Allen Wyatt
(last updated July 11, 2015)

12

There are times when it is beneficial, or even mandatory, to spell numbers out. For instance, you may want to spell out "1234" as "one thousand two hundred thirty four." The following macro, NumberToWords, does just that. It is rather long, but it has to do a lot of checking to put together the proper string. There are actually five macros in the set; the four besides NumberToWords are called by NumberToWords to do the actual conversion.

NumberToWords will convert any number between 0 and 999,999. To use it, simply select the cell (or cells) whose contents you want to convert, then run it. You should note that the cells must contain whole number values, not formulas that result in whole number values. The actual contents of the compliant cells are changed from the original number to a text representation of that number. In other words, this is not a format change, but a value change for those cells.

Sub NumberToWords()
    Dim rngSrc As Range
    Dim lMax As Long
    Dim lCtr As Long
    Dim bNCFlag As Boolean
    Dim sTitle As String, sMsg As String
    Dim vCVal As Variant
    Dim lNumber As Long, sWords As String

    Set rngSrc = ActiveSheet.Range(ActiveWindow.Selection.Address)
    lMax = rngSrc.Cells.Count

    bNCFlag = False
    For lCtr = 1 To lMax
        vCVal = rngSrc.Cells(lCtr).Value
        sWords = ""
        If IsNumeric(vCVal) Then
            If vCVal <> CLng(vCVal) Then
                bNCFlag = True
            Else
                lNumber = CLng(vCVal)
                Select Case lNumber
                Case 0
                    sWords = "Zero"
                Case 1 To 999999
                    sWords = SetThousands(lNumber)
                Case Else
                    bNCFlag = True
                End Select
            End If
        Else
            bNCFlag = True
        End If
        If sWords > "" Then
            rngSrc.Cells(lCtr) = sWords
        End If
    Next lCtr

    If bNCFlag Then
        sTitle = "lNumberToWords Macro"
        sMsg = "Not all cells converted. May not be whole number or may be too large."
        MsgBox sMsg, vbExclamation, sTitle
    End If
End Sub
Private Function SetOnes(ByVal lNumber As Integer) As String
Dim OnesArray(9) As String
    OnesArray(1) = "One"
    OnesArray(2) = "Two"
    OnesArray(3) = "Three"
    OnesArray(4) = "Four"
    OnesArray(5) = "Five"
    OnesArray(6) = "Six"
    OnesArray(7) = "Seven"
    OnesArray(8) = "Eight"
    OnesArray(9) = "Nine"
    SetOnes = OnesArray(lNumber)
End Function
Private Function SetTens(ByVal lNumber As Integer) As String
Dim TensArray(9) As String
    TensArray(1) = "Ten"
    TensArray(2) = "Twenty"
    TensArray(3) = "Thirty"
    TensArray(4) = "Fourty"
    TensArray(5) = "Fifty"
    TensArray(6) = "Sixty"
    TensArray(7) = "Seventy"
    TensArray(8) = "Eighty"
    TensArray(9) = "Ninety"
Dim TeensArray(9) As String
    TeensArray(1) = "Eleven"
    TeensArray(2) = "Twelve"
    TeensArray(3) = "Thirteen"
    TeensArray(4) = "Fourteen"
    TeensArray(5) = "Fifteen"
    TeensArray(6) = "Sixteen"
    TeensArray(7) = "Seventeen"
    TeensArray(8) = "Eighteen"
    TeensArray(9) = "Nineteen"
Dim iTemp1 As Integer
Dim iTemp2 As Integer
Dim sTemp As String
    iTemp1 = Int(lNumber / 10)
    iTemp2 = lNumber Mod 10
    sTemp = TensArray(iTemp1)
    If (iTemp1 = 1 And iTemp2 > 0) Then
        sTemp = TeensArray(iTemp2)
    Else
        If (iTemp1 > 1 And iTemp2 > 0) Then
            sTemp = sTemp + " " + SetOnes(iTemp2)
        End If
    End If
    SetTens = sTemp
End Function
Private Function SetHundreds(ByVal lNumber As Integer) As String
Dim iTemp1 As Integer
Dim iTemp2 As Integer
Dim sTemp As String
    iTemp1 = Int(lNumber / 100)
    iTemp2 = lNumber Mod 100
    If iTemp1 > 0 Then sTemp = SetOnes(iTemp1) + " Hundred"
    If iTemp2 > 0 Then
        If sTemp > "" Then sTemp = sTemp + " "
        If iTemp2 < 10 Then sTemp = sTemp + SetOnes(iTemp2)
        If iTemp2 > 9 Then sTemp = sTemp + SetTens(iTemp2)
    End If
    SetHundreds = sTemp
End Function
Private Function SetThousands(ByVal lNumber As Long) As String
Dim iTemp1 As Integer
Dim iTemp2 As Integer
Dim sTemp As String
    iTemp1 = Int(lNumber / 1000)
    iTemp2 = lNumber Mod 1000
    If iTemp1 > 0 Then sTemp = SetHundreds(iTemp1) + " Thousand"
    If iTemp2 > 0 Then
        If sTemp > "" Then sTemp = sTemp + " "
        sTemp = sTemp + SetHundreds(iTemp2)
    End If
    SetThousands = sTemp
End Function

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8351) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Converting Numbers Into Words.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using Consistent References

Some text references need to be consistent in many places throughout a document. Learn different ways you can ensure that ...

Discover More

Creating a Document Font List

If you want a list of all the fonts used in a document, the answer isn't as simple as you may think. This tip uses macros ...

Discover More

Moving Macros from the Personal Workbook

Need to move a macro out of your Personal.xls workbook and into a regular workbook? You can do it using familiar editing ...

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Reorganizing Data

If you need to consolidate a single column of data into multiple columns of data, you'll love this macro. It provides a way ...

Discover More

Getting a File Name

Does your macro need to allow the user to specify a particular file name that should be used by the macro? Here's a quick ...

Discover More

Finding the Last-Used Cell in a Macro

Ever wonder what the macro-oriented equivalent of pressing Ctrl+End is? Here's the code and some caveats on using it.

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three more than 8?

2017-01-30 00:22:59

narz

i was converting a sixty cents word into 60/100 fraction but still the results is "sixty cents" my question is how to convert it so that the result will be 60/100 only....


2017-01-17 22:15:25

jansen

i use the module, but our central bank required us to put a "ONLY" word in the end of the Check. how can i do this?


2017-01-17 05:35:14

kajappa

Dear Sir
Let me know the words to type in the cell
to convert number to words


2016-08-30 01:17:53

Naeem Bakurally

When converting cents only the word "and" appear before the wordings.
e.g. for 0.25 cents
"and Twenty Five Cents"

Is it possible to add another case where the word "and" does not appear?
i.e for the above example it reads only
"Twenty Five Cents"

Thank you.
Naeem


2016-07-29 09:46:58

poojan

1500000 = fiftyn lakes show but not show this formula


2016-05-30 01:46:57

Kevin

The issue w/this function is that the proper hyphenation isn't done for things like "thirty-five". Is there one you know about that properly formats the words?


2016-04-05 04:15:20

Mujtaba Yasir

Please tell me how the numeric words convert to English translation


2016-04-02 21:50:20

PCN BATTACHARYA

Thank you for your this Programme.

I tested this Macro.. very good result came.. thank you for your kind work and excellency..
At the same time, I want write the numbers in one by one in CELL "A1 - to - A25" as 101 , 102 , 103, . .. like this... and Result Come in same Row in "B" Columns.... sir. i.e... Result will come in B1,B2,B3 . . .andso on.. . sir.

How it make.. How is write macro ? can you Pls.. Help... this..

Pls. reply sir. thank you sir.


2016-04-01 08:19:30

shiv

1. Function ConvertCurrencyToEnglish(ByVal MyNumber)
2. ' Edited by Karthikeyan karthikeyan@livetolearn.in
3. Dim Temp
4. Dim Rupees, Paise
5. Dim DecimalPlace, Count
6.
7. ReDim Place(9) As String
8. Place(2) = " Thousand "
9. Place(3) = " lakh "
10. Place(4) = " Crore "
11.
12.
13. ' Convert MyNumber to a string, trimming extra spaces.
14. MyNumber = Trim(Str(MyNumber))
15.
16. ' Find decimal place.
17. DecimalPlace = InStr(MyNumber, ".")
18.
19. ' If we find decimal place...
20. If DecimalPlace > 0 Then
21. ' Convert Paise
22. Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
23. ' Hi! Note the above line Mid function it gives right portion
24. ' after the decimal point
25. 'if only . and no numbers such as 789. accures, mid returns nothing
26. ' to avoid error we added 00
27. ' Left function gives only left portion of the string with specified places here 2
28.
29.
30. Paise = ConvertTens(Temp)
31.
32.
33. ' Strip off paise from remainder to convert.
34. MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
35. End If
36.
37. Count = 1
38. If MyNumber <> "" Then
39.
40. ' Convert last 3 digits of MyNumber to Indian Rupees.
41. Temp = ConvertHundreds(Right(MyNumber, 3))
42.
43. If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
44.
45. If Len(MyNumber) > 3 Then
46. ' Remove last 3 converted digits from MyNumber.
47. MyNumber = Left(MyNumber, Len(MyNumber) - 3)
48. Else
49. MyNumber = ""
50. End If
51.
52. End If
53.
54. ' convert last two digits to of mynumber
55. Count = 2
56.
57. Do While MyNumber <> ""
58. Temp = ConvertTens(Right("0" & MyNumber, 2))
59.
60. If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
61. If Len(MyNumber) > 2 Then
62. ' Remove last 2 converted digits from MyNumber.
63. MyNumber = Left(MyNumber, Len(MyNumber) - 2)
64.
65. Else
66. MyNumber = ""
67. End If
68. Count = Count + 1
69.
70. Loop
71.
72.
73.
74.
75. ' Clean up dollars.
76. Select Case Rupees
77. Case ""
78. Rupees = "Rupees"
79. Case "One"
80. Rupees = "One Rupee"
81. Case Else
82. Rupees = Rupees & " Rupees"
83. End Select
84.
85. ' Clean up cents.
86. Select Case Paise
87. Case ""
88. Paise = ""
89. Case "One"
90. Paise = " And One Paise"
91. Case Else
92. Paise = " And " & Paise & " Paise"
93. End Select
94.
95. ConvertCurrencyToEnglish = Rupees & Paise
96. End Function
97.
98.
99. Private Function ConvertDigit(ByVal MyDigit)
100. Select Case Val(MyDigit)
101. Case 1: ConvertDigit = "One"
102. Case 2: ConvertDigit = "Two"
103. Case 3: ConvertDigit = "Three"
104. Case 4: ConvertDigit = "Four"
105. Case 5: ConvertDigit = "Five"
106. Case 6: ConvertDigit = "Six"
107. Case 7: ConvertDigit = "Seven"
108. Case 8: ConvertDigit = "Eight"
109. Case 9: ConvertDigit = "Nine"
110. Case Else: ConvertDigit = ""
111. End Select
112.
113. End Function
114.
115. Private Function ConvertHundreds(ByVal MyNumber)
116. Dim Result As String
117.
118. ' Exit if there is nothing to convert.
119. If Val(MyNumber) = 0 Then Exit Function
120.
121. ' Append leading zeros to number.
122. MyNumber = Right("000" & MyNumber, 3)
123.
124. ' Do we have a hundreds place digit to convert?
125. If Left(MyNumber, 1) <> "0" Then
126. Result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
127. End If
128.
129. ' Do we have a tens place digit to convert?
130. If Mid(MyNumber, 2, 1) <> "0" Then
131. Result = Result & ConvertTens(Mid(MyNumber, 2))
132. Else
133. ' If not, then convert the ones place digit.
134. Result = Result & ConvertDigit(Mid(MyNumber, 3))
135. End If
136.
137. ConvertHundreds = Trim(Result)
138. End Function
139.
140.
141. Private Function ConvertTens(ByVal MyTens)
142. Dim Result As String
143.
144. ' Is value between 10 and 19?
145. If Val(Left(MyTens, 1)) = 1 Then
146. Select Case Val(MyTens)
147. Case 10: Result = "Ten"
148. Case 11: Result = "Eleven"
149. Case 12: Result = "Twelve"
150. Case 13: Result = "Thirteen"
151. Case 14: Result = "Fourteen"
152. Case 15: Result = "Fifteen"
153. Case 16: Result = "Sixteen"
154. Case 17: Result = "Seventeen"
155. Case 18: Result = "Eighteen"
156. Case 19: Result = "Nineteen"
157. Case Else
158. End Select
159. Else
160. ' .. otherwise it's between 20 and 99.
161. Select Case Val(Left(MyTens, 1))
162. Case 2: Result = "Twenty "
163. Case 3: Result = "Thirty "
164. Case 4: Result = "Forty "
165. Case 5: Result = "Fifty "
166. Case 6: Result = "Sixty "
167. Case 7: Result = "Seventy "
168. Case 8: Result = "Eighty "
169. Case 9: Result = "Ninety "
170. Case Else
171. End Select
172.
173. ' Convert ones place digit.
174. Result = Result & ConvertDigit(Right(MyTens, 1))
175. End If
176.
177. ConvertTens = Result
178. End Function


2016-03-01 15:56:32

Abrar Ahmad

thanks


2016-01-26 00:28:12

Tee

Hi Allen,

Please tell me how to run the above macro to convert numbers to words in excel. I don't understand how to get the code into the excel workbook that I want to use it in.


Thanks,
Tee


2015-10-30 06:08:11

DIVYESH

plz tell me how to convert numbers into words in Ms Excel


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.