converting numbers to written text 3

[marduk813]

Is there a function that will convert "$1234.56" to "one thousand two hundred thirty four dollars and fifty six cents"?

 

[rmikesmith]

marduk813,

Nope. You'd need to write your own, which would not be trivial. In the book VBA Develpers Handbook by Ken Getz and Mike Gilbert, they provide a function to convert and integer to an ordinal number representation. Your requirement would be more complex.

Regards,
Mike

 

[DaleWatson123321]

Some years ago, I wrote a series of formulas in Lotus 123 to convert a number to a spelled-out-number.

In Excel the following "custom function" is a big improvement. I won't take credit for writing this. I picked it up somewhere, but unfortunately can't recall who the creator was.

For the benefit of anyone new to VBA... Steps:

1) Go to the VBA Editor - by holding down the <Alt> key and hitting <F11>.

2) From the menu, choose: Insert - Module.

3) Copy and paste the following code into your module.

4) Use <Alt> <F11> to exit from the VBA Editor.

5) In your worksheet, enter the function
=spellnumber(A1) - where A1 is the cell containing your number.

Option Explicit

'================
' Main Function
'================
Function SpellNumber(ByVal MyNumber)
Dim Dollars, Cents, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = &quot; Thousand &quot;
Place(3) = &quot; Million &quot;
Place(4) = &quot; Billion &quot;
Place(5) = &quot; Trillion &quot;

'String representation of number
MyNumber = Trim(Str(MyNumber))

'Position of decimal place - 0 if none
DecimalPlace = InStr(MyNumber, &quot;.&quot

'Converts cents and sets MyNumber to dollar amount
If DecimalPlace > 0 Then
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & &quot;00&quot;, 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1
Do While MyNumber <> &quot;&quot;
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> &quot;&quot; Then Dollars = Temp & Place(Count) & Dollars
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = &quot;&quot;
End If
Count = Count + 1
Loop

Select Case Dollars
Case &quot;&quot;
Dollars = &quot;No Dollars&quot;
Case &quot;One&quot;
Dollars = &quot;One Dollar&quot;
Case Else
Dollars = Dollars & &quot; Dollars&quot;
End Select

Select Case Cents
Case &quot;&quot;
Cents = &quot; and No Cents&quot;
Case &quot;One&quot;
Cents = &quot; and One Cent&quot;
Case Else
Cents = &quot; and &quot; & Cents & &quot; Cents&quot;
End Select

SpellNumber = Dollars & Cents
End Function

'===========================================
' Converts a number from 100-999 into text
'===========================================
Function GetHundreds(ByVal MyNumber)
Dim Result As String

If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right(&quot;000&quot; & MyNumber, 3)

'Converts to hundreds place
If Mid(MyNumber, 1, 1) <> &quot;0&quot; Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & &quot; Hundred &quot;
End If

'Converts to tens and ones place
If Mid(MyNumber, 2, 1) <> &quot;0&quot; Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If

GetHundreds = Result
End Function

'===========================================
' Converts number from 10 to 99 into text
'===========================================
Function GetTens(TensText)
Dim Result As String

Result = &quot;&quot; 'null out temporary function value,
If Val(Left(TensText, 1)) = 1 Then 'if value between 10-19
Select Case Val(TensText)
Case 10: Result = &quot;Ten&quot;
Case 11: Result = &quot;Eleven&quot;
Case 12: Result = &quot;Twelve&quot;
Case 13: Result = &quot;Thirteen&quot;
Case 14: Result = &quot;Fourteen&quot;
Case 15: Result = &quot;Fifteen&quot;
Case 16: Result = &quot;Sixteen&quot;
Case 17: Result = &quot;Seventeen&quot;
Case 18: Result = &quot;Eighteen&quot;
Case 19: Result = &quot;Nineteen&quot;
Case Else
End Select
Else 'if value between 20-99
Select Case Val(Left(TensText, 1))
Case 2: Result = &quot;Twenty &quot;
Case 3: Result = &quot;Thirty &quot;
Case 4: Result = &quot;Forty &quot;
Case 5: Result = &quot;Fifty &quot;
Case 6: Result = &quot;Sixty &quot;
Case 7: Result = &quot;Seventy &quot;
Case 8: Result = &quot;Eighty &quot;
Case 9: Result = &quot;Ninety &quot;
Case Else
End Select
Result = Result & GetDigit _
(Right(TensText, 1)) 'get ones place
End If
GetTens = Result
End Function

'===========================================
' Converts number from 1 to 9 into text
'===========================================
Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = &quot;One&quot;
Case 2: GetDigit = &quot;Two&quot;
Case 3: GetDigit = &quot;Three&quot;
Case 4: GetDigit = &quot;Four&quot;
Case 5: GetDigit = &quot;Five&quot;
Case 6: GetDigit = &quot;Six&quot;
Case 7: GetDigit = &quot;Seven&quot;
Case 8: GetDigit = &quot;Eight&quot;
Case 9: GetDigit = &quot;Nine&quot;
Case Else: GetDigit = &quot;&quot;
End Select
End Function

=========================================

I hope this helps anyone who has need of such a function.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rmikesmith]

Dale,

Sweet!! I was in the process of taking a crack at this when I saw your reply. Now I'm going to delve into the code. Even though you didn't write this I'm giving you a star for pulling this from your bag of goodies.

Regards,
Mike

 

[marduk813]

Dale, thank you very much. I had gone searching for such a beast and the one I found, while shorter than the one you posted, did not work as well.
Thanks again!

Jas

 

[JustinEzequiel]

see

http://support.microsoft.com/default.aspx?scid=KB;en-us;173287&

it has its limitations
on my machine (Word 97), it cannot handle values in millions