Suggestions Example file
Convert numbers to text

1 Sometimes you need a conversion of numbers to Text

In the English language the textual representation of numbers is rather regular.
Numbers are being split into groups of 3, thousands, millions, billions, etc.
The fist digit in each group indicates the number of hundreds, the second the amount of tens, the third the number of 'singles'.

The converted text will be concatenated in this way:

7 hundred 7 ty 7

Some irregularities:

- some combinations have a different (own) name: 11, 12, 13, 14, 20, 30, 40, 50.
- in numbers between 10 and 20 the 'single' precedes the decade (teen).

I tried to write an application independent method.
You can apply it in Word, Excel, Access, Powerpoint, Outlook, etc.

The attachment file contains 3 different functions: F_convert, F_convert_dec and F_convert_dec_suf.
The function F_convert is meant to convert integers exclusively.
The function F_convert_dec is meant for integers or numbers, containing 2 decimals maximum.
The function F_convert_dec_suf is meant for integers and numbers containing 2 decimals, including the option to add a prefix, suffix, or special decimalseparator.

Every function calls a common function 'mats' to retrieve texts.
Each function can be called by a macro: see e.g. Sub M_tst()

In Excel you can call each function as a User Defined Function (UDF), provided the functions resides in a macromodule.
You can call the function in a cell formula: e.g. =F_convert(row())

2 Description of the code

2 1 The function F_convert

The length of the number will be adjusted so that it's length is a multifold of 3: '4' becomes '004'; '12' becomes '012'; '1230455' becomes '001230455'.

Every group of three is being analysed using the function 'mats'; the results will be stored in array sp.
The array sp retrieves the following information:
- text for the amount of hundreds: the first number in the group of 3: sp(0)
- text for every idiosyncratic name > 10 : the last 2 digits in the group of 3: sp(1)
- text for the 'singles': the third digit in the group of 3: sp(2)
- text for idiosyncratic names >19 (20, 30, 50) to combine with single units: the second digit in the group of 3, with a "0" added: sp(3)
- text for the decade: the second digit in the group of 3: sp(4)

The results in array sp are being combined.
- if the first digit in the group of 3 is greater than 0 add the string 'hundred' to the result in sp(0)
- if the group doesn't contain any decades or singles: ready

- if the last 2 digits have an idiosyncratic name sp(1) add it; ready

- if the second digit in the group is '1': add to the singles sp(2) the string "teen"; ready
- if the second digit in the group is not '1' and the decade has an idiosyncratic name sp(3) then add the idiosycratic name sp(3) added plus the singles sp(2).
- if the second digit in the group is not '1' and the decade has no idiosyncratic name then add the string 'ty' to the decade number followed by the singles sp(2)

After that some 'polishing'.

2.2 The function F_mats

The string " 0 1One 2Two 3Three 4Four..... 50fify 80Eighty" is being split by the number (y).
The second element in resulting array starts with the first word after the splitting element; in this case: 'Thirteen 15Fifteen ....'
The first array element has the indexnumber 0, the second element the index 1.
Now we split this second element(1) using the 'space'.
In the resulting array the first element (index number 0) contains the word 'thirteen'.

In VBA you can write these steps:
sp=split(" 0 1One 2Two 3Three 4Four ..... 50Fifty 80Eighty",y)
sq=split(sp(1))
F_mats=sq(0)
You can condense this to a oneliner:
F_mats=split(split(" 0 1One 2Two 3Three 4Four ..... 50Fifty 80Eighty ")(1))(0)

3 The VBA code in the attachment

Function F_convert(y)
F_convert="Invalid input"
If y = "" Or Val(y) = 0 Then Exit Function

c00 = Format(Val(1 * y), String(3 * ((Len(Format(Val(1 * y))) - 1) \ 3 + 1), "0"))

For j = 1 to Len(c00) \ 3
x = Mid(c00, 3*(j-1) + 1, 3)

sp = Array(F_mats(Left(x, 1)), F_mats(Val(Right(x, 2))), F_mats(Right(x, 1)), F_mats(Mid(x, 2, 1) & "0"), F_mats(Mid(x, 2, 1)))
c01 = c01 & IIf(sp(0) = "", "", sp(0) & " Hundred ") & IIf(Right(x, 2) = "00", "", IIf(sp(1) <> "", sp(1), IIf(Mid(x, 2, 1) = "1", Trim(sp(2)) & "teen", IIf(sp(3) = "", sp(4) & "ty", sp(3)) & " " & sp(2)))) & Choose(Len(c00) \ 3 - j + 1, "", " Thousand ", " Million ", " Billion ")
Next

F_convert = IIf(c01 = "", "zero", Replace(c01, " ", " "))
End Function
Function F_mats(y)
On Error Resume Next

F_mats = Split(Split(" 0 1One 2Two 3Three 4Four 5Five 6Six 7Seven 8Eight 9Nine 10Ten 11Eleven 12Twelve 13Thirteen 15Fifteen 20Twenty 30Thirty 50Fifty 80Eighty ", y)(1))(0)
End Function
Sub M_tst()
MsgBox F_convert(InputBox(String(4, vbLf) & "enter a number", "snb")), , "snb"
End Sub