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)

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

Text Doesn't Wrap at Margin in Draft View

If you are using Word in Draft view, and the text on the screen doesn't wrap at the right margin like it should, the culprit ...

Discover More

Pay Attention to Case when Searching for ASCII Codes

Word allows you to search for specific ASCII codes in a document. If you use codes to search for alphabetic characters, you ...

Discover More

Repeating In a Macro

Macros are often used to process information stored in documents. Usually the processing involves some sort of iterative ...

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)

Using the Status Bar

When developing a macro, you may want to display on the status bar what the macro is doing. Here's how to use this important ...

Discover More

Hiding Excel in VBA

Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel ...

Discover More

Finding Cells Filled with a Particular Color

Do you need to find cells that are formatted with a particular color? How you accomplish this task depends on your version of ...

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 for this tip:

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)

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.

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.

Links and Sharing
Share