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
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:
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.
The First and Last Word on Word! Bestselling For Dummies author Dan Gookin puts his usual fun and friendly candor back to work to show you how to navigate Word 2013. Spend more time working and less time trying to figure it all out! Check out Word 2013 For Dummies today!
When creating macros, you often need to offer a series of choices to a user. This tip demonstrates how easy it is to ...
Discover MoreWord keeps track of identifying and statistical information about a document and makes that information available in the ...
Discover MoreVBA is great at working with both strings and numbers. At some point, you may have a number you need to convert to a ...
Discover MoreFREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.”
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.
Visit the WordTips channel on YouTube
FREE SERVICE: Get tips like this every week in WordTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments