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

# Converting Numbers Into Words

Written by Allen Wyatt (last updated December 7, 2020)
This tip applies to Excel 97, 2000, 2002, and 2003

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 bNCFlag As Boolean
Dim sTitle As String, sMsg As String
Dim vCVal As Variant
Dim lNumber As Long, sWords As String

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) = "Forty"
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 (2270) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) 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

Applying Standard Headers and Footers to a Document

When you attach a template to a document, it doesn't modify the documents headers or footers. This tip provides three ...

Discover More

Setting Row Height

When you enter information into a row on a worksheet, Excel automatically adjusts the height of the row based on what you ...

Discover More

Seeing the Height of a Row

Want to see the exact height of a row? This tip provides a quick and precise way that you can see that height.

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

Running a Macro in a Number of Workbooks

Got a macro that you need to run on each of a number of workbooks? Excel provides a number of ways to go about this task, ...

Discover More

Selecting the First Cell In a Row

When creating macros, you'll often have a need to select different cells in the worksheet. Here's how to select the first ...

Discover More

Determining Differences Between Dates

Macros are often used to process the data in a worksheet. If that data includes dates that need to be processed, you'll ...

Discover More
##### Subscribe

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

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}] (all 7 characters, in the sequence shown) in your comment text. Youâ€™ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. 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 one less than 7?

2016-06-04 11:16:03

B. Helsel

How could this be expanded to show even larger numbers (>999,999) or small numbers (<0)?

##### This Site

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