Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Showing Numbers in English Words (2003)

    I have searched these boards and Google and I just want to make sure that I'm not missing anything here because I am quite amazed by this fact. From what I understand Excel does not offer either of the following 2 abilities:

    1) Convert a number to it's emglish format. For example convert the number 1 to the English value one or the number 62 to the English word Sixty-two or even Sixty two.

    2) Autofill numbers represented as English Words. For example if in 3 consecutive cells in a row I have the following 3 values: One, Two & Three, Excel's Autofill is unable to figure out that the value in the next cell should be FOur.

    I'm just looking for confirmation that the above 2 statements are correct. I ask because I'm just amazed that with all of it's whiz bang features that Excel is still unable to do either of these. #1 in particular is quite puzzling to me. I have seen various code on the internet for this including Microsoft's own KB article # 210586. Even after publishing it's own method for doing this as far back as Office 2000 there is still no built-in methid in Excel to convert numbers to English Words.

    Thanks
    Ed

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Showing Numbers in English Words (2003)

    There is no built-in facility for either. As mentioned you could create a function (and MS has listed one) for converting. The second could be done but you would have to create the custom list yourself.

    Steve

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

    Re: Showing Numbers in English Words (2003)

    You are correct in number 1, Excel does not have any built in function to convert numbers to text. The VBA code below is what I use to do the conversion. You use it like this if the number is in A1:

    <code>
    =NumberToText(A1)
    </code>

    to get "Sixty Two" if A1 contains 62.

    If you are doing Dollars and cents, then you can do this:

    <code>
    =NumberToText(A1,"Dollars","Cents")
    </code>

    to get "Sixty Two Dollars and Fifty Cents" if A1 contains 62.5.

    <code>
    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


    </code>

    Excel can do what you ask in your second question. If you select Options from the Tools menu, and then click on the "Custom Lists" tab, you can define a list of the text numbers. If you do this, then Excel will be able to do what you asked. However, the range of numbers will be limited to the list you define.
    Legare Coleman

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Showing Numbers in English Words (2003)

    What you've done with the UDF and VBA is very good. However, is there a way to insert the grammatically correct "-" where appropriate or is that difficult or impossibe?
    For example: thirty-six rather than thirty six.

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

    Re: Showing Numbers in English Words (2003)

    Does this do what you want:

    <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
    If Right(Result, 1) = "-" Then Result = Left(Result, Len(Result) - 1)

    HundredsToText = Result

    End Function
    </pre>

    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Showing Numbers in English Words (2003)

    Legare,
    First let me thank you for your contribution to this post. I appreciate your posting your code even after a working version was mentioned. Your code looks shorter and cleaner. Do you know if it performs better then the code from Microsoft's KB Article? I also wanted to mention that when I try to copy the second instance of your code it copies as a single line where as the first has carriage returns and copies and pastes exactly as it looks. Are the differences simple enough for you to quickly pint out? I can reformat the second code instance if need be but I figured if the changes were small enough it would be easier if you could just point them out.

    Lastly I wanted to know if it is OK to use your code? I have a client who uses an Excel Add-in my company produces and they are looking for a way to convert some of the cells values from numbers to their English Words. I figured it is OK to pass this code along to them since you posted it however I felt it was the proper thing to check with you first. I prefer explicit to implicit any day.

    A big thanks to Steve and Kevin(KWeaver); Appreaciate you guys replies too.

    Thanks Again!
    Ed

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts

    Re: Showing Numbers in English Words (2003)

    Perfectly! Thanks for the update.

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

    Re: Showing Numbers in English Words (2003)

    The code pastes as one line because it is HTML when you copy it from the lounge and the VBA editor doesn't handle that very well. There is a way to post code on the lounge board that can be copied and pasted, but there is a bug in the lounge code that handles that which makes it very difficult to post larger blocks of code in that format (you get an error when you try to post the code). Therefore, I had to post that block of code in another format that displays OK, but won't directly copy and paste. To copy and paste that block of code, copy it as usual and then paste it into either Word or WordPad. Copy the code from there and paste it into the VBA editor. It should paste as displayed in the lounge.

    You are free to use any code that I post in the Lounge, as long as you do not sell it. You can give a copy to your client, but you can not sell it to them.
    Legare Coleman

Posting Permissions

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