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

by Allen Wyatt
(last updated June 4, 2016)

1

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

    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) = "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

Creating a New Document in VBA

When working with documents in a macro, it makes sense that you may need to create a document from time to time. Here's how ...

Discover More

Converting Coded Dates into Real Dates

Sometimes the format in which you receive data is not the same format that would be optimal for Excel. For instance, you ...

Discover More

Mail Merge and Data Source Documents become Unattached

When you create a mail merge document, you attach it to a data source that is the basis for the information to be merged with ...

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 (menu)

Conditionally Displaying a Message Box

You can, from within your macros, easily display a message box containing a message of your choice. If you want to display ...

Discover More

Counting All Characters

Need to know how many characters there are in a workbook? You can find out easily with the handy macro introduced in this ...

Discover More

Creating Charts in VBA

Most charts you create in Excel are based on information stored in a worksheet. You can also create charts based on ...

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 seven more than 5?

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.

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.