+ Reply to Thread
Results 1 to 5 of 5

Convert number into words

  1. #1
    Blackwar
    Guest

    Convert number into words

    Hi,
    Can any one help me to convert numeric into words (without Currency USD,
    EURO, just words), e. g.,

    15,015.27
    Fifteen thousand fifteen and twenty seven only

    Rgds
    Blackwar

  2. #2
    Paul B
    Guest

    Re: Convert number into words

    Blackwar, have a look here for how to do it,

    http://www.xldynamic.com/source/xld.xlFAQ0004.html

    --
    Paul B
    Always backup your data before trying something new
    Please post any response to the newsgroups so others can benefit from it
    Feedback on answers is always appreciated!
    Using Excel 2002 & 2003

    "Blackwar" <[email protected]> wrote in message
    news:[email protected]...
    > Hi,
    > Can any one help me to convert numeric into words (without Currency USD,
    > EURO, just words), e. g.,
    >
    > 15,015.27
    > Fifteen thousand fifteen and twenty seven only
    >
    > Rgds
    > Blackwar




  3. #3
    Blackwar
    Guest

    Re: Convert number into words

    Thnx 4 ur help Paul
    but can u help me more about where i can find the solution in the web site
    you mentioned, web site is in french and i unable to find the code or
    solution there.

    please help

    "Paul B" wrote:

    > Blackwar, have a look here for how to do it,
    >
    > http://www.xldynamic.com/source/xld.xlFAQ0004.html
    >
    > --
    > Paul B
    > Always backup your data before trying something new
    > Please post any response to the newsgroups so others can benefit from it
    > Feedback on answers is always appreciated!
    > Using Excel 2002 & 2003
    >
    > "Blackwar" <[email protected]> wrote in message
    > news:[email protected]...
    > > Hi,
    > > Can any one help me to convert numeric into words (without Currency USD,
    > > EURO, just words), e. g.,
    > >
    > > 15,015.27
    > > Fifteen thousand fifteen and twenty seven only
    > >
    > > Rgds
    > > Blackwar

    >
    >
    >


  4. #4
    Paul B
    Guest

    Re: Convert number into words

    Blackwar, the first two are in english and the other one has a button on it
    for english, but here is one way,

    To put in this macro, from your workbook right-click the workbook's icon and
    pick View Code. This icon is to the left of the "File" menu this will open
    the VBA editor, in Project Explorer click on your workbook name, if you
    don't see it press CTRL + r to open the Project Explorer, then go to insert,
    module, and paste the code below in the window that opens on the right hand
    side, press Alt and Q to close this window and go back to your workbook and
    press alt and F8, this will bring up a box to pick the Macro from, click on
    the Macro name to run it. If you are using excel 2000 or newer you may
    have to change the macro security settings to get the macro to run. To
    change the security settings go to tools, macro, security, security level
    and set it to medium.
    After you do this then on your worksheet use it like this =SpellNumber(A1)
    with the number you want to spell out in A1




    Option Explicit
    'Main Function
    Function SpellNumber(ByVal MyNumber)
    'Use like =SpellNumber(A1)
    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



    --
    Paul B
    Always backup your data before trying something new
    Please post any response to the newsgroups so others can benefit from it
    Feedback on answers is always appreciated!
    Using Excel 2002 & 2003

    "Blackwar" <[email protected]> wrote in message
    news:[email protected]...
    > Thnx 4 ur help Paul
    > but can u help me more about where i can find the solution in the web site
    > you mentioned, web site is in french and i unable to find the code or
    > solution there.
    >
    > please help
    >
    > "Paul B" wrote:
    >
    > > Blackwar, have a look here for how to do it,
    > >
    > > http://www.xldynamic.com/source/xld.xlFAQ0004.html
    > >
    > > --
    > > Paul B
    > > Always backup your data before trying something new
    > > Please post any response to the newsgroups so others can benefit from it
    > > Feedback on answers is always appreciated!
    > > Using Excel 2002 & 2003
    > >
    > > "Blackwar" <[email protected]> wrote in message
    > > news:[email protected]...
    > > > Hi,
    > > > Can any one help me to convert numeric into words (without Currency US

    D,
    > > > EURO, just words), e. g.,
    > > >
    > > > 15,015.27
    > > > Fifteen thousand fifteen and twenty seven only
    > > >
    > > > Rgds
    > > > Blackwar

    > >
    > >
    > >




  5. #5
    Blackwar
    Guest

    Re: Convert number into words

    Many Many thanks 2 u Paul
    But the problem is still to be resolved, i already tested the same code &
    it's working fine but only with the USD currency

    Actually i maintain my client record from different countries and therefore
    i need a general code to convert the numbers (amount) in words like

    15,015.27
    > > > > Fifteen thousand fifteen and twenty seven only


    no dollars & cents, Euro & cents, & Pound & Pence etc.
    just the pure words

    hope you will help me to do so as you tried very professionally before

    Thnx

    "Paul B" wrote:

    > Blackwar, the first two are in english and the other one has a button on it
    > for english, but here is one way,
    >
    > To put in this macro, from your workbook right-click the workbook's icon and
    > pick View Code. This icon is to the left of the "File" menu this will open
    > the VBA editor, in Project Explorer click on your workbook name, if you
    > don't see it press CTRL + r to open the Project Explorer, then go to insert,
    > module, and paste the code below in the window that opens on the right hand
    > side, press Alt and Q to close this window and go back to your workbook and
    > press alt and F8, this will bring up a box to pick the Macro from, click on
    > the Macro name to run it. If you are using excel 2000 or newer you may
    > have to change the macro security settings to get the macro to run. To
    > change the security settings go to tools, macro, security, security level
    > and set it to medium.
    > After you do this then on your worksheet use it like this =SpellNumber(A1)
    > with the number you want to spell out in A1
    >
    >
    >
    >
    > Option Explicit
    > 'Main Function
    > Function SpellNumber(ByVal MyNumber)
    > 'Use like =SpellNumber(A1)
    > 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
    >
    >
    >
    > --
    > Paul B
    > Always backup your data before trying something new
    > Please post any response to the newsgroups so others can benefit from it
    > Feedback on answers is always appreciated!
    > Using Excel 2002 & 2003
    >
    > "Blackwar" <[email protected]> wrote in message
    > news:[email protected]...
    > > Thnx 4 ur help Paul
    > > but can u help me more about where i can find the solution in the web site
    > > you mentioned, web site is in french and i unable to find the code or
    > > solution there.
    > >
    > > please help
    > >
    > > "Paul B" wrote:
    > >
    > > > Blackwar, have a look here for how to do it,
    > > >
    > > > http://www.xldynamic.com/source/xld.xlFAQ0004.html
    > > >
    > > > --
    > > > Paul B
    > > > Always backup your data before trying something new
    > > > Please post any response to the newsgroups so others can benefit from it
    > > > Feedback on answers is always appreciated!
    > > > Using Excel 2002 & 2003
    > > >
    > > > "Blackwar" <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > > Hi,
    > > > > Can any one help me to convert numeric into words (without Currency US

    > D,
    > > > > EURO, just words), e. g.,
    > > > >
    > > > > 15,015.27
    > > > > Fifteen thousand fifteen and twenty seven only
    > > > >
    > > > > Rgds
    > > > > Blackwar
    > > >
    > > >
    > > >

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1