Amount converted into amount in words with fractional number

mideleoc

New Member
Joined
Dec 8, 2005
Messages
1
Is there a way to convert numbers to words. Example $700.00 to Seven Hundred Dollars or $7.50 to Seven dollars and 50/100 dollars.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

DominicB

Well-known Member
Joined
Oct 3, 2005
Messages
1,569
Good morning mideleoc

A lightly modified version of the code contained in the link that Andrew supplied, to give the fraction you requested. This formula contains four optional currencies - the default being GBP.

=Spell(A1,"USD") will return the value in dollars.

Code:
Function Spell(ByVal MyNumber, Optional Curr As String = "GBP") As String
Dim Pounds, Pence, Temp
Dim DecimalPlace, Count
Dim Curr1, Curr2, Curr3, Curr4 As String
ReDim Place(9) As String
If Curr = "GBP" Then
Curr1 = "Pounds": Curr2 = "Pence": Curr3 = "Pound": Curr4 = "Penny"
End If
If Curr = "EUR" Then
Curr1 = "Euros": Curr2 = "Cents": Curr3 = "Euro": Curr4 = "Cent"
End If
If Curr = "USD" Then
Curr1 = "Dollars": Curr2 = "Cents": Curr3 = "Dollar": Curr4 = "Cent"
End If
If Curr = "INR" Then
Curr1 = "Rupees": Curr2 = "Paisa": Curr3 = "Rupee": Curr4 = "Paisa"
End If
Application.Volatile True
Place(2) = "Thousand "
Place(3) = "Million "
Place(4) = "Billion "
Place(5) = "Trillion "
MyNumber = Trim(Str(MyNumber))
DecimalPlace = InStr(MyNumber, ".")
If DecimalPlace > 0 Then
Pence = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If
Count = 1
Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Pounds = Temp & Place(Count) & Pounds
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1
Loop
Select Case Pounds
Case ""
Pounds = "No " & Curr1
Case "One"
Pounds = "One " & Curr3
Case Else
Pounds = Pounds & Curr1
End Select
Spell = Pounds & " " & Pence & "/100"
End Function

Private Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & "Hundred and "
End If
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

Private Function GetTens(TensText)
Dim Result As String
Result = ""
If Val(Left(TensText, 1)) = 1 Then
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 Else
End Select
Else
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))
End If
GetTens = Result
End Function

Private 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

HTH

DominicB
 

Forum statistics

Threads
1,137,292
Messages
5,680,645
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top