ExcelTips (Ribbon Interface)

# Converting Numbers Into Words

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.

```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.

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

### Leave your own comment:

 *Name: Email: Notify me about new comments ONLY FOR THIS TIP Notify me about new comments ANYWHERE ON THIS SITE Hide my email address *Text: *What is 5+3 (To prevent automated submissions and spam.)

### Comments for this tip:

Naeem Bakurally    30 Aug 2016, 01:17
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
poojan    29 Jul 2016, 09:46
1500000 = fiftyn lakes show but not show this formula
Kevin    30 May 2016, 01:46
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?
Mujtaba Yasir    05 Apr 2016, 04:15
Please tell me how the numeric words convert to English translation
PCN BATTACHARYA    02 Apr 2016, 21:50
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.
shiv    01 Apr 2016, 08:19
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
Abrar Ahmad    01 Mar 2016, 15:56
thanks
Tee    26 Jan 2016, 00:28
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
DIVYESH    30 Oct 2015, 06:08
plz tell me how to convert numbers into words in Ms Excel

# Our Company

Sharon Parq Associates, Inc.

# Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Excel Products

Word Products

# Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.