Results 1 to 4 of 4

Convert Numbers to Text

This is a discussion on Convert Numbers to Text within the Excel Questions forums, part of the Question Forums category; Dear Sir, How to convert Number into text e.g 123.00 One Hundred and Twently Three Dollars...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    2

    Default

    Dear Sir,
    How to convert Number into text

    e.g 123.00

    One Hundred and Twently Three Dollars

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,855

    Default

    On 2002-07-21 13:34, S.Ameen Ahmed wrote:
    Dear Sir,
    How to convert Number into text

    e.g 123.00

    One Hundred and Twently Three Dollars
    Try NUMTEXT included in the MOREFUNC add-in, dowloadable from:

    http://longre.free.fr/english/index.html

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Grand Rapids, MI
    Posts
    326

    Default

    On 2002-07-21 13:34, S.Ameen Ahmed wrote:
    Dear Sir,
    How to convert Number into text

    e.g 123.00

    One Hundred and Twently Three Dollars
    Code:
    Option Explicit
    
    '****************' Main Function *'****************
    'Use this funtion to spell numbers with words.
    'The other functions are used by this one.
    'The only other function function which can be called is GetHundreds.
    
    Function SpellNumber(ByVal MyNumber)
    Dim Dollars, Cents, Temp
    Dim DecimalPlace, Count
    ReDim Place(9) As String
    Place(2) = " Thousand "
    Place(3) = " Million "
    Place(4) = " Billion "
    Place(5) = " Trillion " ' String representation of amount
    MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
    DecimalPlace = InStr(MyNumber, ".")
    'Convert cents and set MyNumber to dollar amount
    If DecimalPlace > 0 Then
    Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
    MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
    End If
    Count = 1
    Do While MyNumber <> ""
    Temp = GetHundreds(Right(MyNumber, 3))
    If Temp <> "" Then Dollars = Temp & Place(Count) & Dollars
    If Len(MyNumber) > 3 Then
    MyNumber = Left(MyNumber, Len(MyNumber) - 3)
    Else
    MyNumber = ""
    End If
    Count = Count + 1
    Loop
    Select Case Dollars
    Case ""
    Dollars = "No Dollars"
    Case "One"
    Dollars = "One Dollar"
    Case Else
    Dollars = Dollars & " Dollars"
    End Select
    Select Case Cents
    Case ""
    Cents = " and No Cents"
    Case "One"
    Cents = " and One Cent"
    Case Else
    Cents = " and " & Cents & " Cents"
    End Select
    SpellNumber = Dollars & Cents
    End Function
    '*******************************************
    ' Converts a number from 100-999 into text *
    '*******************************************
    Function GetHundreds(ByVal MyNumber)
    Dim Result As String
    If Val(MyNumber) = 0 Then Exit Function
    MyNumber = Right("000" & MyNumber, 3) 'Convert the hundreds place
    If Mid(MyNumber, 1, 1) <> "0" Then
    Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
    End If
    'Convert the tens and ones place
    If Mid(MyNumber, 2, 1) <> "0" Then
    Result = Result & GetTens(Mid(MyNumber, 2))
    Else
    Result = Result & GetDigit(Mid(MyNumber, 3))
    End If
    GetHundreds = Result
    End Function
    '*********************************************
    ' Converts a number from 10 to 99 into text. *
    '*********************************************
    Function GetTens(TensText)
    Dim Result As String
    Result = "" 'null out the temporary function value
    If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19
    Select Case Val(TensText)
    Case 10: Result = "Ten"
    Case 11: Result = "Eleven"
    Case 12: Result = "Twelve"
    Case 13: Result = "Thirteen"
    Case 14: Result = "Fourteen"
    Case 15: Result = "Fifteen"
    Case 16: Result = "Sixteen"
    Case 17: Result = "Seventeen"
    Case 18: Result = "Eighteen"
    Case 19: Result = "Nineteen"
    Case Else
    End Select
    Else ' If value between 20-99
    Select Case Val(Left(TensText, 1))
    Case 2: Result = "Twenty "
    Case 3: Result = "Thirty "
    Case 4: Result = "Forty "
    Case 5: Result = "Fifty "
    Case 6: Result = "Sixty "
    Case 7: Result = "Seventy "
    Case 8: Result = "Eighty "
    Case 9: Result = "Ninety "
    Case Else
    End Select
    Result = Result & GetDigit _
    (Right(TensText, 1)) 'Retrieve ones place
    End If
    GetTens = Result
    End Function
    '*******************************************
    ' Converts a number from 1 to 9 into text. *
    '*******************************************
    Function GetDigit(Digit)
    Select Case Val(Digit)
    Case 1: GetDigit = "One"
    Case 2: GetDigit = "Two"
    Case 3: GetDigit = "Three"
    Case 4: GetDigit = "Four"
    Case 5: GetDigit = "Five"
    Case 6: GetDigit = "Six"
    Case 7: GetDigit = "Seven"
    Case 8: GetDigit = "Eight"
    Case 9: GetDigit = "Nine"
    Case Else: GetDigit = ""
    End Select
    End Function
    Post this in a module then in a cell use =spellnumber(123.00) or =spellnumber(cell) where cell is the cell that has the number or =spellnumber(textbox1.value)

    HTH

    Derrick


  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582

    Default

    Here is the link to the Microsoft Code



    http://support.microsoft.com/support.../Q140/7/04.ASP
    '"XL: How to Convert a Numeric Value into English Words"

    http://support.microsoft.com/support...s/Q95/6/40.ASP
    '"How to Convert Currency or Numbers into English Words

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
  •  


DMCA.com