Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Figures into words (Excell 2000)

    I am looking for a way to convert a number to words i.e. 1254 = "one thousand two hundred and fifty four". Is there a function to do this or is it necessary to write a macro? Any pointers?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figures into words (Excell 2000)

    You need a VBA function for this. See XL2000: How to Convert a Numeric Value into English Words.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figures into words (Excell 2000)

    Here are the functions that I use:

    <pre>Function NumberToText(Num As Variant, Optional vCurName As Variant, Optional vCent As Variant) As Variant
    Dim TMBT As Variant
    Dim sNum As String, sDec As String, sHun As String, IC As Integer
    Dim Result As String, sCurName As String, sCent As String


    If Application.IsNumber(Num) = False Then
    NumberToText = CVErr(xlValue)
    Exit Function
    End If

    If IsMissing(vCurName) Then
    sCurName = ""
    Else
    sCurName = Trim(CStr(vCurName))
    End If
    If IsMissing(vCent) Then
    sCent = ""
    Else
    sCent = Trim(CStr(vCent))
    End If


    TMBT = Array("", "Thousand", "Million", "Billion", "Trillion", "Quadrillion", "Quintillion", "Sextillion")

    If IsMissing(sCent) Or IsNull(sCent) Then
    sNum = Format(Application.Round(Num, 0), "0")
    Else
    sNum = Format(Application.Round(Num, 2), "0.00")
    sDec = Right(sNum, 2)
    sNum = Left(sNum, Len(sNum) - 3)
    If CInt(sDec) <> 0 Then
    sDec = "and " & Trim(HundredsToText(CVar(sDec)) & " " & sCent)
    Else
    sDec = ""
    End If
    End If

    IC = 0
    While Len(sNum) > 0
    sHun = Right(sNum, 3)
    sNum = Left(sNum, Application.Max(Len(sNum) - 3, 0))
    If CInt(sHun) <> 0 Then
    Result = Trim(Trim(HundredsToText(CVar(sHun)) & " " & TMBT(IC)) & " " & Result)
    End If
    IC = IC + 1
    Wend
    Result = Trim(Result & " " & sCurName)
    Result = Trim(Result & " " & sDec)

    NumberToText = Result

    End Function

    Function HundredsToText(Num As Integer) As String
    Dim Units As Variant, Teens As Variant, Tens As Variant
    Dim I As Integer, IUnit As Integer, ITen As Integer, IHundred As Integer
    Dim Result As String

    Units = 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", "Fourty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

    Result = ""
    IUnit = Num Mod 10
    I = Int(Num / 10)
    ITen = I Mod 10
    IHundred = Int(I / 10)
    If IHundred > 0 Then
    Result = Units(IHundred) & " Hundred"
    End If
    If ITen = 1 Then
    Result = Result & " " & Teens(IUnit)
    Else
    If ITen > 1 Then
    Result = Trim(Result & " " & Tens(ITen) & " " & Units(IUnit))
    Else
    Result = Trim(Result & " " & Units(IUnit))
    End If
    End If

    HundredsToText = Result

    End Function
    </pre>

    Legare Coleman

  4. #4
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figures into words (Excell 2000)

    Thanks very much. Should save me a lot of time.

  5. #5
    New Lounger
    Join Date
    Apr 2003
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Figures into words (Excell 2000)

    Thanks very much. This should save me a great deal of time and effort.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Figures into words (Excell 2000)

    If I am not mistaken, does not the ASAP Utilities program do this? I somehow recall that it had this functionality too!
    Regards,
    Rudi

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Figures into words (Excell 2000)

    Why don't you check it? And why do you post this in a thread from 2003?

Posting Permissions

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