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.
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.
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: 
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.
Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!
When your macro is humming along, minding its own business, a user watching the screen may not see any activity and therefore ...
Discover MoreThe colors used in a Excel are not as simple as they used to be. Here are some ideas relative to working with those colors in ...
Discover MoreWhen opening a workbook, you may want to make sure that a particular worksheet is always displayed first. The only way to ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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
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
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
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2017 Sharon Parq Associates, Inc.
Comments