Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: coversion formula

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    formula to convert dollar to text.

    eg. $50.00 convert to "Dollar Fifty only"

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Florida
    Posts
    82
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •