Converting Words into Numbers

Written by Allen Wyatt (last updated May 28, 2022)
This tip applies to Word 2007, 2010, 2013, 2016, 2019, Word in Microsoft 365, and 2021


2

Gabe has used macros before to convert numbers (such as 355) into words (three-hundred and fifty-five). He would find it very helpful if he had a macro that could do the opposite and convert the words he selects back into numbers. He isn't sure how to create such a macro, however.

A macro to handle this type of conversion is more involved than what one might expect, but it can be made a bit easier by limiting the macro so that it handles only whole numbers. The following two macros (one a subroutine and the other a function called from the subroutine) will convert any selected text up to 999,999,999.

Sub TextToNumbers()
    Dim NText() As Variant
    Dim NB() As Variant
    Dim aRange As Range
    Dim k As Long
    Dim L As Long
    Dim LL As Long
    Dim LLL As Long
    Dim s As String
    Dim j As Long
    Dim m As Long
    Dim mm As Long
    Dim mmm As Long
    Dim gA() As Long
    Dim gB() As Long
    Dim wCount As Long
    Dim ThousandsComma As Boolean

    ThousandsComma = True  ' --- gives thousands commas ----

    NText = Array("AND", "-", "ONE", "TWO", "THREE", "FOUR", "FIVE", _
      "SIX", "SEVEN", "EIGHT", "NINE", "TEN", "ELEVEN", "TWELVE", "THIRTEEN", _
      "FOURTEEN", "FIFTEEN", "SIXTEEN", "SEVENTEEN", "EIGHTEEN", "NINETEEN", _
      "TWENTY", "THIRTY", "FORTY", "FIFTY", "SIXTY", "SEVENTY", "EIGHTY", _
      "NINETY", "HUNDRED", "THOUSAND", "MILLION", "$%$%")

    NB = Array(0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, _
      11, 12, 13, 14, 15, 16, 17, 18, 19, _
      20, 30, 40, 50, 60, 70, 80, 90, _
      100, 1000, 1000000, 0)

    ' ------ Check selected text for validity ---------------
    Set aRange = Selection.Range
    If Right(aRange.Text, 1) = Chr(13) Then
        aRange.MoveEnd Unit:=wdCharacter, Count:=-1
    End If
    s = aRange.Text
    If Right(s, 1) = "." Then aRange.MoveEnd Unit:=wdCharacter, Count:=-1
    If aRange.Paragraphs.Count > 1 Then
        MsgBox "Selection cannot span paragraphs"
        Exit Sub
    End If
    If Trim(aRange.Text) = "" Then
        MsgBox "Selection does not include a word"
        Exit Sub
    End If
    ' ------------------------------

    wCount = aRange.Words.Count
    ReDim gA(wCount + 1)
    ReDim gB(wCount + 1)

    For k = 1 To wCount   ' aRange.Words.Count
        s = UCase(Trim(aRange.Words(k)))
        If s = Chr(13) Or s = "" Then
            gB(k) = 1
            gA(k) = 0
            GoTo FF2
        End If
        For m = 0 To UBound(NText) - 1
            If s = NText(m) Then GoTo FF1
        Next m
        MsgBox "The word " & """" & aRange.Words(k) & """" & " is non numeric"
        Exit Sub
FF1:
        gA(k) = NB(m)
        If m > UBound(NText) - 5 Then
            gB(k) = 2
        Else
            gB(k) = 1
        End If
FF2:
    Next k
   ' Arrays gA & gB hold word values and the operator on that word

    wCount = removeZeros(gA(), gB(), wCount)

    ' ----- combine multiple numbers and remove zero entries -----
    For k = 1 To wCount - 1
        If gB(k) = 1 And gB(k + 1) = 1 Then
            gA(k + 1) = gA(k + 1) + gA(k)
            gA(k) = 0
        End If
    Next k
    wCount = removeZeros(gA(), gB(), wCount)

    m = 1
    Do
        m = m + 1
    Loop Until m > wCount Or gA(m) = 1000000
    If m <= wCount Then ' 1000000 is at gA(m)
        ' check that there are no 1000s
        j = 1
        Do
            j = j + 1
        Loop Until j >= m Or gA(j) = 1000
        If j < m Then
            MsgBox "Cannot process thousands of millions"
            Exit Sub
        End If

        j = 1
        Do
            j = j + 1
        Loop Until j >= m Or gA(j) = 100
        If j < m Then
            gA(j) = gA(j) * gA(j - 1)
            gA(j - 1) = 0
            gB(j) = 1
        End If
        LL = 0
        For j = 1 To m - 1
            L = L + gA(j)
            gA(j) = 0
            gB(j) = 0
        Next j
        gA(m) = L * gA(m)
        gB(m) = 1
    Else
        m = 0
    End If ' 1000000 processed

    mm = j + 1
    If mm <= wCount Then
        Do
            mm = mm + 1
        Loop Until mm > wCount Or gA(mm) = 1000
    End If
    If mm <= wCount Then ' 1000 is at ga(mm)
        j = m + 1
        Do
            j = j + 1
        Loop Until j >= mm Or gA(j) = 100
        If j < mm Then ' have 100 preceeding 1000
            gA(j) = gA(j) * gA(j - 1)
            gA(j - 1) = 0
            gB(j) = 1
        End If
        LL = 0
        For j = m + 1 To mm - 1
            LL = LL + gA(j)
            gA(j) = 0
            gB(j) = 1
        Next j
        gA(mm) = LL * gA(mm)
        gB(mm) = 1
    Else
        mm = m
    End If ' 1000 is at mmth entry

    mmm = mm + 1
    If mmm <= wCount Then
        Do
            mmm = mmm + 1
        Loop Until mmm > wCount Or gA(mmm) = 100
    End If
    If mmm <= wCount Then ' 100 is at gA(mmm)
        gA(mmm) = gA(mmm - 1) * gA(mmm)
        gA(mmm - 1) = 0
        gB(mmm) = 1
    Else
        mm = m
    End If ' 100 is at mmmth entry

    LLL = 0
    For k = 1 To wCount
        LLL = LLL + gA(k)
    Next k

    If ThousandsComma Then
        s = Format(LLL, "Standard")
        s = Left(s, Len(s) - 3)
    Else
        s = Str(LLL)
    End If
    aRange.Text = Trim(s)
    aRange.Select
End Sub
Function removeZeros(gA() As Long, gB() As Long, ByVal wCount As Long) As Long
    Dim q As Long
    Dim z As Long

    For q = wCount To 1 Step -1
        If gA(q) = 0 Then
            z = q
            Do While z < wCount
                gA(z) = gA(z + 1)
                gB(z) = gB(z + 1)
                z = z + 1
            Loop
            wCount = wCount - 1
        End If
    Next q
    removeZeros = wCount
End Function

To use the macro, select the words you want to convert and then run TextToNumbers. The macro is destructive; it converts the actual words to their numeric equivalent. (If you want to maintain the original words, copy them before running the macro.)

Note:

If you would like to know how to use the macros described on this page (or on any other page on the WordTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

WordTips is your source for cost-effective Microsoft Word training. (Microsoft Word is the most popular word processing software in the world.) This tip (12893) applies to Microsoft Word 2007, 2010, 2013, 2016, 2019, Word in Microsoft 365, and 2021.

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 Chart

Creating a graphic chart based on your worksheet data is easy. This tip provides a couple of different ways you can start ...

Discover More

Getting Rid of Negative Zero Amounts

Have you ever seen a worksheet in which some zero values have a negative sign in front of them? There's a reason for ...

Discover More

Limiting the Scope of the Undo Command

As you make edits in Excel, the program remembers your actions so that you can later undo them. If you have multiple ...

Discover More

Learning Made Easy! Quickly teach yourself how to format, publish, and share your content using Word 2013. With Step by Step, you set the pace, building and practicing the skills you need, just when you need them! Check out Microsoft Word 2013 Step by Step today!

More WordTips (ribbon)

Understanding the For ... Next Structure

Spend any time creating Word macros, and sooner or later you will need to repeat some of your programming code a certain ...

Discover More

Finding a Change in Typeface

When you format a document, you can go so far as to change the font of each character in the document. This may be ...

Discover More

Putting Template Macros in a Document

You can easily store your macros in a template. If you create a document based on the template and then the document is ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, 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}] (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 eight more than 3?

2022-05-29 06:42:12

Ken Endacott

The macro will correctly convert:
three hundred fifty-five
three hundred fifty five
three hundred and fifty-five
three hundred and fifty five
One million five
One million and five


2022-05-29 01:50:49

L Michelle Rapier

I was taught the correct way to write a 3- or more digit number does not include the word “and.” For example, 355 should be written as “three hundred fifty-five.”


This Site

Got a version of Word that uses the ribbon interface (Word 2007 or later)? This site is for you! If you use an earlier version of Word, visit our WordTips site focusing on the menu interface.

Videos
Subscribe

FREE SERVICE: Get tips like this every week in WordTips, 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.