I'm not sure if you've found your solution yet or not but this is what I used. If you create a macro in Visual Basic, cut and paste the following text. This will create a function for you called spellnumber. You can then create a formula like =spellnumber(A1) to convert 1000 into One Thousand
Function SpellNumber(ByVal MyNumber
Dim Dollars, Cents, Tem
Dim DecimalPlace, Coun
ReDim Place(9) As Strin
Place(2) = " Thousand
Place(3) = " Million
Place(4) = " Billion
Place(5) = " Trillion
' String representation of amount
MyNumber = Trim(Str(MyNumber)
' Position of decimal place 0 if none
DecimalPlace = InStr(MyNumber, "."
' Convert cents and set MyNumber to dollar amount
If DecimalPlace > 0 The
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) &
"00", 2)
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1)
End I
Count =
Do While MyNumber <> "
Temp = GetHundreds(Right(MyNumber, 3)
If Temp <> "" Then Dollars = Temp & Place(Count) & Dollar
If Len(MyNumber) > 3 The
MyNumber = Left(MyNumber, Len(MyNumber) - 3
Els
MyNumber = "
End I
Count = Count +
Loo
Select Case Dollar
Case "
Dollars = "No Dollars
Case "One
Dollars = "One Dollar
Case Els
Dollars = Dollars & " Dollars
End Selec
Select Case Cent
Case "
Cents = " and No Cents
Case "One
Cents = " and One Cent
Case Els
Cents = " and " & Cents & " Cents
End Selec
SpellNumber = Dollars & Cent
End Functio
'******************************************
' Converts a number from 100-999 into text
'******************************************
Function GetHundreds(ByVal MyNumber
Dim Result As Strin
If Val(MyNumber) = 0 Then Exit Functio
MyNumber = Right("000" & MyNumber, 3
' Convert the hundreds place
If Mid(MyNumber, 1, 1) <> "0" The
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred
End I
' Convert the tens and ones place
If Mid(MyNumber, 2, 1) <> "0" The
Result = Result & GetTens(Mid(MyNumber, 2)
Els
Result = Result & GetDigit(Mid(MyNumber, 3)
End I
GetHundreds = Resul
End Functio
'********************************************
' Converts a number from 10 to 99 into text.
'********************************************
Function GetTens(TensText
Dim Result As Strin
Result = "" ' Null out the temporary function value
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19..
Select Case Val(TensText
Case 10: Result = "Ten
Case 11: Result = "Eleven
Case 12: Result = "Twelve
Case 13: Result = "Thirteen
Case 14: Result = "Fourteen
Case 15: Result = "Fifteen
Case 16: Result = "Sixteen
Case 17: Result = "Seventeen
Case 18: Result = "Eighteen
Case 19: Result = "Nineteen
Case Els
End Selec
Else ' If value between 20-99..
Select Case Val(Left(TensText, 1)
Case 2: Result = "Twenty
Case 3: Result = "Thirty
Case 4: Result = "Forty
Case 5: Result = "Fifty
Case 6: Result = "Sixty
Case 7: Result = "Seventy
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function
----- Harlan Grove wrote: -----
Gord Dibben said:
No built-in Function, but you can build your own User Defined Function using
the info at:
....
For the heck of it, this can be done with worksheet functions at the cost of
a 2-column by 28-row range for a lookup table. That table would contain
0 ' <- single quote only, so evaluates to ""
1 one
2 two
3 three
4 four
5 five
6 six
7 seven
8 eight
9 nine
10 ten
11 eleven
12 twelve
13 thirteen
14 fourteen
15 fifteen
16 sixteen
17 seventeen
18 eighteen
19 nineteen
20 twenty
30 thirty
40 forty
50 fifty
60 sixty
70 seventy
80 eighty
90 ninety
If this were named X, then the formula to handle up to 999,999,999 would be
=TRIM(IF(D1>1000000,IF(MOD(INT(D1/100000000),10),LOOKUP(MOD(INT(D1/100000000
),10),X)
&" hundred
","")&LOOKUP(MOD(INT(D1/1000000),100),X)&IF(AND(MOD(INT(D1/1000000),100)>20,
MOD(INT(D1/1000000),10)),"-"&LOOKUP(MOD(INT(D1/1000000),10),X),"")&" million
","")&
IF(D1>1000,IF(MOD(INT(D1/100000),10),LOOKUP(MOD(INT(D1/100000),10),X)&"
hundred ","")&
LOOKUP(MOD(INT(D1/1000),100),X)&IF(AND(MOD(INT(D1/1000),100)>20,MOD(INT(D1/1
000),10)),
"-"&LOOKUP(MOD(INT(D1/1000),10),X),"")&" thousand
","")&IF(MOD(INT(D1/100),10),
LOOKUP(MOD(INT(D1/100),10),X)&" hundred
","")&LOOKUP(MOD(D1,100),X)&IF(AND(MOD(D1,100)>20,
MOD(D1,10)),"-"&LOOKUP(MOD(D1,10),X),""))
