By John Walkenbach

If you’ve ever needed to display a number written out as text, you probably discovered that Excel does not offer such a function. When Excel fails to deliver, it’s often possible to correct the deficiency by using VBA. Here’s a VBA function, named SPELLDOLLARS, that you can use in worksheet formulas.

Excel examples

Here are some examples of SPELLDOLLARS.

image0.jpg

The VBA code

To use this code, press Alt+F11 to activate Visual Basic Editor. Then choose Insert → Module to insert a new VBA module. Copy the code and paste it into the new module.

Function SPELLDOLLARS(cell) As Variant
‘  Returns a value, spelled out in words
  Dim Dollars As String, Cents As String
  Dim TextLen As Long, Pos As Long
  Dim Temp As String
  Dim iHundreds As Long, iTens As Long, iOnes As Long
  Dim Ones As Variant, Teens As Variant, Tens As Variant
  Dim Units(2 To 5) As String
  Dim bHit As Boolean, NegFlag As Boolean
‘  Is it a non-number or empty cell?
  If Not IsNumeric(cell) Or 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