Spelling out numbers

[mvidas]

Hi Everyone,

Though this isn't exactly an excel-only question, I think this subforum gets a lot of hits so I figured I'd post here.

Before I go through the trouble of writing a function that takes a Long number and converts it to string format (123 = "One Hundred Twenty-Three"), does anyone know of an existing one out there anywhere? I don't mind writing it, but as there are a lot of possibilities I thought I might at least first check to see if someone knows of something.

I'll probably start writing one anyways after I post this, but I could even compare whatever anyone finds to what I write. If you're bored, feel free to write one too, I guess :)

Matt

[JKwan]

Try this:
http://cpap.com.br/orlando/#SpellNumber

[mvidas]

For some reason that link isn't working for me? I'll try from home later, I'm getting a 404 here at work though.

I did come up with a routine for this though, so I'm going to mark this as solved (for my reminder to add this to the KB later though)


Function NumberString(ByVal aNumber As Variant) As String
Dim AnArr() As String, tStr As String, i As Long, j As Long, tStr2 As String, aName As String
tStr2 = Format(aNumber, "#,##0")
AnArr = Split(tStr2, ",")
j = 1
For i = UBound(AnArr) To LBound(AnArr) Step -1
Select Case j
Case 1: aName = ""
Case 2: aName = " Thousand"
Case 3: aName = " Million"
Case 4: aName = " Billion"
Case 5: aName = " Trillion"
Case 6: aName = " Quadrillion"
Case 7: aName = " Quintillion"
Case 8: aName = " Sextillion"
Case 9: aName = " Septillion"
Case 10: aName = " Octillion"
Case 11: aName = " Nonillion"
Case 12: aName = " Decillion"
Case 13: aName = " Undecillion"
Case 14: aName = " Duodecillion"
Case 15: aName = " Tredecillion"
Case 16: aName = " Quattordecillion"
Case 17: aName = " Quindecillion"
Case 18: aName = " Sexdecillion"
Case 19: aName = " Septendecillion"
Case 20: aName = " Octodecillion"
Case 21: aName = " Novemdecillion"
Case 22: aName = " Vigintillion"
End Select
tStr2 = GetAString(AnArr(i))
If tStr2 <> "" Then tStr = tStr2 & aName & IIf(tStr <> "", ", " & tStr, "")
j = j + 1
Next
NumberString = tStr
End Function
Function GetAString(ByVal aNumber2 As Long) As String
Dim tempStr As String, One As String, Two As String, Three As String
tempStr = Format(aNumber2, "000")
One = Left(tempStr, 1)
Two = Mid(tempStr, 2, 1)
Three = Right(tempStr, 1)
If One <> "0" Then
GetAString = GetAString & Choose(CSng(One), "One", "Two", "Three", "Four", "Five", _
"Six", "Seven", "Eight", "Nine") & " Hundred"
End If
If Two <> "0" Then
If CLng(Two) > 1 Then
GetAString = GetAString & IIf(GetAString <> "", " ", "") & Choose(CSng(Two), "", "Twenty", "Thirty", "Forty", _
"Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Else
If Three = "0" Then
GetAString = GetAString & " Ten"
Else
GetAString = GetAString & IIf(GetAString <> "", " ", "") & Choose(CSng(Three), "Eleven", "Twelve", _
"Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
End If
Exit Function
End If
End If
If Three <> "0" Then
GetAString = GetAString & IIf(GetAString <> "", " ", "") & Choose(CSng(Three), "One", "Two", "Three", "Four", _
"Five", "Six", "Seven", "Eight", "Nine")
End If
End Function

Matt

[Bob Phillips]

Before I go through the trouble of writing a function that takes a Long number and converts it to string format (123 = "One Hundred Twenty-Three"), does anyone know of an existing one out there anywhere? I don't mind writing it, but as there are a lot of possibilities I thought I might at least first check to see if someone knows of something.

I'll probably start writing one anyways after I post this, but I could even compare whatever anyone finds to what I write. If you're bored, feel free to write one too, I guess

See http://www.xldynamic.com/source/xld.xlFAQ0004.html

[johnske]

Hi Matt,

Without looking, I seem to recall there's one in the kb also

Regards,
John :)

[mvidas]

Good link to have on your website, xld, I searched through microsoft and couldn't find that there. If I get some time later I'll probably browse the rest of your site as well.

John,
I thought so too, but upon searching I didn't find it. I assumed I either used the wrong search terms or imagined seeing an entry for it :)

As this thought of mine was a whim, and that no one should ever have to spell out 'vigintillion', I don't really care much about this anymore :) I didn't have a real use for it, just wanted to see if someone had a link to an existing one.

I did find a limit to the "format" function though. Oddly enough, Format won't treat numbers larger than 28 digits as the number sent.
For example, run the following
Debug.Print Format("9999999999999999999999999999", "#,##0")
it returns "9,999,999,999,999,999,999,999,999,999". Now, run this (one more 9 added):
Debug.Print Format("99999999999999999999999999999", "#,##0")
it returns "100,000,000,000,000,000,000,000,000,000" (the given number + 1). Using "5" instead of "9":
Debug.Print Format("5555555555555555555555555555", "#,##0")
returns "5,555,555,555,555,555,555,555,555,555", and
Debug.Print Format("55555555555555555555555555555", "#,##0")
returns "55,555,555,555,555,555,555,555,555,555". Strangely (not so strange considering excel's sig fig limit):
Debug.Print Format("555555555555555555555555555555", "#,##0")
Debug.Print Format("5555555555555555555555555555555", "#,##0")
return "555,555,555,555,556,000,000,000,000,000" and "5,555,555,555,555,560,000,000,000,000,000"

OK, my bored-and-playing-with-vba time is over :)

[johnske]

Hi Matt,

When you're feeling bored again here's the link to that kb entry (http://www.vbaexpress.com/kb/getarticle.php?kb_id=735) (title was a little unusual, so it dint show in a search. Found it manually).

Regards,
John :)

[mvidas]

I'll take a look at that right after I'm done posting this. I fixed my format issue, the following had to be done at the top of NumberString


'tStr2 = Format(aNumber, "#,##0")
For i = Len(CStr(aNumber)) - 2 To 1 Step -3
tStr2 = Mid(CStr(aNumber), i, 3) & IIf(tStr2 = "", "", ",") & tStr2
j = i
Next 'i
If j <> 1 Then tStr2 = Mid(CStr(aNumber), 1, j - 1) & IIf(tStr2 = "", "", ",") & tStr2

Now allowing you to make such strings as "Nine Hundred Ninety Nine Vigintillion, Nine Hundred Ninety Nine Novemdecillion, Nine Hundred Ninety Nine Octodecillion, Nine Hundred Ninety Nine Septendecillion, Nine Hundred Ninety Nine Sexdecillion, Nine Hundred Ninety Nine Quindecillion, Nine Hundred Ninety Nine Quattordecillion, Nine Hundred Ninety Nine Tredecillion, Nine Hundred Ninety Nine Duodecillion, Nine Hundred Ninety Nine Undecillion, Nine Hundred Ninety Nine Decillion, Nine Hundred Ninety Nine Nonillion, Nine Hundred Ninety Nine Octillion, Nine Hundred Ninety Nine Septillion, Nine Hundred Ninety Nine Sextillion, Nine Hundred Ninety Nine Quintillion, Nine Hundred Ninety Nine Quadrillion, Nine Hundred Ninety Nine Trillion, Nine Hundred Ninety Nine Billion, Nine Hundred Ninety Nine Million, Nine Hundred Ninety Nine Thousand, Nine Hundred Ninety Nine"
Apparently boredom hit again. Can't have that much longer, I need to start work soon!