Seeking a process/script/app or other tool for converting numbers with commas to text

Hi all,

First my situation: I’m an editor in the publishing world who uses Microsoft Word for Mac (always the latest version) as my primary text-editing tool, so any solution would have to take that into account or function as part of a workflow whose results would eventually end up there.

Okay, here’s my problem: I’m in need of a process/script/app or some other tool that will convert numbers with commas (e.g., 12,000, 48,000,000) to text (e.g., twelve thousand, forty-eight million).

I already have and use the BigCardText macro (https://wordribbon.tips.net/T007755_Converting_Numbers_to_Text), which is wonderful, and others like it exist, but I have yet to find one that works on numbers with commas, which is always true of the texts I edit. I would readily pay for a tool of any kind that does what I need.

As it is, if I come across a number like 87, I can hit a key combination, and the BigCardText macro changes the number to “eighty-seven.” Fantastic! But, if the number is, say, 87,000,000, I have to stop, highlight or delete the number, and type out “eighty-seven million.” This is unbearably tedious and ripe for automation.

Many thanks in advance,
Kerry

I"m not familiar with the macro you mentioned. Can it handle 87000000 (without the commas)? If the only issue is stripping the commas and passing to your macro, you could use regex. Keyboard Maestro, for example, can read the clipboard and remove everything that isn’t a digit (0-9):

Modifying the select and store sections like this should modify the selection to extend beyond the word breaks imposed by the commas, and strip them out with a simple replace.

' Select the full number in which the insertion point is located
Selection.MoveEndWhile Cset:="0123456789,.", Count:=wdForward
Selection.MoveStartWhile Cset:="0123456789,.", Count:=wdBackward

' Store the digits in a variable
sDigits = Replace(Trim(Selection.Text), ",", "")

This will then run as before for “87,000,000”, but will actually use “87000000” as the value to convert. But I would note that the translation is imperfect. I doubt the fact I’m running it on Windows causes any issues, but the result I get is “eighty-seven million zero” rather than “eighty-seven million”. This is Word’s internal field conversion doing this, so the only way around it I think would be to write an entirely new function to parse and process the numbers rather than rely on Word’s field formatting to do it.

Hope that helps.

Yes, it can handle the number without the commas, which is the reason I can’t use it for that situation. However, you have sent me down a hopefully fruitful path. Thanks!

Yes, if I manually remove the commas, I also get the “eight-seven million zero” result with the macro as it currently is coded. So if I apply your modification and make the Word macro part of a Keyboard Maestro macro that can delete the unfortunate “zero” problem, I think it might do what I need. Thanks!

I took a closer look to see what was going on, and it is because of that large number split in the code that that fragmentation is occurring. A quick Google search yielded some VB code that was a lot better at handling the conversion. I’ve made some very minor tweaks to have it run in VBA/Word, and put a subroutine on it to match the selection code amendment I posted above. I figured it would be better to fix the issue than to partially patch the result.

Sub ConvertNumberToWords()
    'Select Content
    Selection.MoveEndWhile Cset:="0123456789,.", Count:=wdForward
    Selection.MoveStartWhile Cset:="0123456789,.", Count:=wdBackward
    
    ' Store the digits in a variable
    Selection.TypeText Text:=WordNum(Trim(Selection.Text))
End Sub


Function WordNum(MyNumber As Double) As String
Numbers = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen")
Tens = Array("", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety")

Dim DecimalPosition As Integer, ValNo As Variant, StrNo As String
Dim NumStr As String, n As Integer, Temp1 As String, Temp2 As String
' This macro was written by Chris Mead - www.MeadInKent.co.uk

If Abs(MyNumber) > 999999999 Then
 WordNum = "Value too large"
 Exit Function
End If

' String representation of amount (excl decimals)
NumStr = Right("000000000" & Trim(Str(Int(Abs(MyNumber)))), 9)
ValNo = Array(0, Val(Mid(NumStr, 1, 3)), Val(Mid(NumStr, 4, 3)), Val(Mid(NumStr, 7, 3)))

For n = 3 To 1 Step -1 'analyse the absolute number as 3 sets of 3 digits
StrNo = Format(ValNo(n), "000")

If ValNo(n) > 0 Then
Temp1 = GetTens(Val(Right(StrNo, 2)))
If Left(StrNo, 1) <> "0" Then
 Temp2 = Numbers(Val(Left(StrNo, 1))) & " hundred"
 If Temp1 <> "" Then Temp2 = Temp2 & " and "
Else
 Temp2 = ""
End If

If n = 3 Then
 If Temp2 = "" And ValNo(1) + ValNo(2) > 0 Then Temp2 = "and "
 WordNum = Trim(Temp2 & Temp1)
End If
If n = 2 Then WordNum = Trim(Temp2 & Temp1 & " thousand " & WordNum)
If n = 1 Then WordNum = Trim(Temp2 & Temp1 & " million " & WordNum)

End If
Next n

NumStr = Trim(Str(Abs(MyNumber)))

' Values after the decimal place
DecimalPosition = InStr(NumStr, ".")
Numbers(0) = "Zero"
If DecimalPosition > 0 And DecimalPosition < Len(NumStr) Then
 Temp1 = " point"
 For n = DecimalPosition + 1 To Len(NumStr)
  Temp1 = Temp1 & " " & Numbers(Val(Mid(NumStr, n, 1)))
 Next n
 WordNum = WordNum & Temp1
End If

If Len(WordNum) = 0 Or Left(WordNum, 2) = " p" Then
 WordNum = "Zero" & WordNum
End If
End Function

Function GetTens(TensNum As Integer) As String
Numbers = Array("", "one", "two", "three", "four", "five", "six", "seven", "eight", "nine", "ten", "eleven", "twelve", "thirteen", "fourteen", "fifteen", "sixteen", "seventeen", "eighteen", "nineteen")
Tens = Array("", "", "twenty", "thirty", "forty", "fifty", "sixty", "seventy", "eighty", "ninety")

' Converts a number from 0 to 99 into text.
If TensNum <= 19 Then
 GetTens = Numbers(TensNum)
Else
 Dim MyNo As String
 MyNo = Format(TensNum, "00")
 GetTens = Tens(Val(Left(MyNo, 1))) & " " & Numbers(Val(Right(MyNo, 1)))
End If
End Function

For my testing it seems to do a much better job, dealing automatically with commas, fractional parts, including “and” in the correct places. The only things it misses out on from my quick tests is hyphentaion of some of the compound words and negative numbers.

Running ConvertNumberToWords gave me the following conversions:

  • 87,000,000 -> eighty seven million
  • 87,000,001 -> eighty seven million and one
  • 87,000,000 -> eighty seven million and eleven
  • 87,654,321 -> eighty seven million six hundred and fifty four thousand three hundred and twenty one
  • 0001 -> one
  • 0011 -> eleven
  • 0111 -> one hundred and eleven
  • 1111 -> one thousand one hundred and eleven
  • 321.456 -> three hundred and twenty one point four five six

Hopefully, that should give you a stronger solution.

Thank you so much! This is fantastic. I really, really appreciate it.

One last question on this: Is it possible to insert hyphens in the compound numbers? That is, have “eighty seven” come out as eighty-seven," etc.?

As above, that was exactly one of the things I highlighted to you, but yes you could account for that. You just need to describe it in VBA code.

Every instance where you would want to insert a hyphen, you would have do a replace. I used a replace function in that very first amendment I posted.

One approach could be putting in appropriate iterations to check for all of the possibilities where it would be necessary and running the replace.

It is possible there could be a way to do it through coding some grammar rules to work it out, but I suspect that would take a bit longer to figure out for little speed and maintainability improvement.

I apologize for overlooking your mention of hyphenation. I got excited and was reading too fast. Thank you so much again.