coversion formula

moqsad

New Member
Joined
Mar 30, 2002
Messages
3
formula to convert dollar to text.

eg. $50.00 convert to "Dollar Fifty only"
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
This was taken from a book by Jonh Walkenbach. You can change the wording to match your request..

Function SPELLDOLLARS(cell) As Variant

Dim Dollars As String
Dim Cents As String
Dim TextLen As Integer
Dim Temp As String
Dim Pos As Integer
Dim iHundreds As Integer
Dim iTens As Integer
Dim iOnes As Integer
Dim Units(2 To 5) As String
Dim bHit As Boolean
Dim Ones As Variant
Dim Teens As Variant
Dim Tens As Variant
Dim NegFlag As Boolean

' Is it a non-number?
If Not IsNumeric(cell) Then
SPELLDOLLARS = CVErr(xlErrValue)
Exit Function
End If

' Is it negative?
If cell < 0 Then
NegFlag = True
cell = Abs(cell)
End If

Dollars = Format(cell, "###0.00")
TextLen = Len(Dollars) - 3

' Is it too large?
If TextLen > 15 Then
SPELLDOLLARS = CVErr(xlErrNum)
Exit Function
End If

' Do the cents part
Cents = Right(Dollars, 2) & "/100 Dollars"
If cell < 1 Then
SPELLDOLLARS = Cents
Exit Function
End If

Dollars = Left(Dollars, TextLen)

Ones = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine")
Teens = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
Tens = Array("", "", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

Units(2) = "Thousand"
Units(3) = "Million"
Units(4) = "Billion"
Units(5) = "Trillion"

Temp = ""

For Pos = 15 To 3 Step -3
If TextLen >= Pos - 2 Then
bHit = False
If TextLen >= Pos Then
iHundreds = Asc(Mid$(Dollars, TextLen - Pos + 1, 1)) - 48
If iHundreds > 0 Then
Temp = Temp & " " & Ones(iHundreds) & " Hundred"
bHit = True
End If
End If
iTens = 0
iOnes = 0

If TextLen >= Pos - 1 Then
iTens = Asc(Mid$(Dollars, TextLen - Pos + 2, 1)) - 48
End If

If TextLen >= Pos - 2 Then
iOnes = Asc(Mid$(Dollars, TextLen - Pos + 3, 1)) - 48
End If

If iTens = 1 Then
Temp = Temp & " " & Teens(iOnes)
bHit = True
Else
If iTens >= 2 Then
Temp = Temp & " " & Tens(iTens)
bHit = True
End If
If iOnes > 0 Then
If iTens >= 2 Then
Temp = Temp & "-"
Else
Temp = Temp & " "
End If
Temp = Temp & Ones(iOnes)
bHit = True
End If
End If
If bHit And Pos > 3 Then
Temp = Temp & " " & Units(Pos 3)
End If
End If
Next Pos

SPELLDOLLARS = Trim(Temp) & " and " & Cents
If NegFlag Then SPELLDOLLARS = "(" & SPELLDOLLARS & ")"


End Function
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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
Back
Top