Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Write a number in words

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Write a number in words

    Dear sir,
    I would like to know the formula / function for writing the series of numbers (eg. 1 to 1000000) in words in Excel work sheet. For example, if in cell A1 the number 1 is there, in cell B1 I should get "ONE", if in cell A1 number 2 is there, in cell B1 I should get "TWO" and so on up to 1000000.
    Thanking you,

    VASUDEVAN
    [email protected]

  • #2
    Re: Write a number in words

    A common requirement. The best code I've seen for this is shown in the website below:

    http://www.mentalis.org/tips/tip108.shtml

    Comment


    • #3
      Re: Write a number in words

      Hi Vasu,

      If you are looking for an Indian style, here is a UDF from Yogi Anand.

      Function SpellIndian(ByVal MyNumber)

      '**** Yogi Anand -- ANAND Enterprises -- Rochester Hills MI 48309 -- 248-375-5710 [url]www.anandent.com[/url]
      '**** Last updated 03-Oct-2003
      '**** SpellIndian (modified on 20-Sep-2003 to 1) show Rupees to precede, and to show "" for 0 paise)
      '**** ySpellRupees (on 20-Nov-2002)
      '**** Excel UDF to spell Indian Currency -- Rupees and Paise into text
      '**** Indian currency starts off with 1000s, and after that only with 100s
      '**** 1000 (Thousand) -- 1,00,000 (Lac or Lakh) -- 1,00,00,000 (Crore) -- 1,00,00,00,000 (Arab)
      '**** (this UDF is based on SpellNumber by Microsoft)
      '****************' Main Function *'****************

      Dim Rupees, Paise, Temp
      Dim DecimalPlace, Count
      ReDim Place(9) As String
      Place(2) = " Thousand "
      Place(3) = " Lac "
      Place(4) = " Crore "
      Place(5) = " Arab " ' String representation of amount
      MyNumber = Trim(Str(MyNumber)) ' Position of decimal place 0 if none
      DecimalPlace = InStr(MyNumber, ".")
      'Convert Paise and set MyNumber to Rupee amount
      If DecimalPlace > 0 Then
      Paise = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2))
      MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
      End If
      Count = 1
      Do While MyNumber <> ""
      If Count = 1 Then Temp = GetHundreds(Right(MyNumber, 3))
      If Count > 1 Then Temp = GetHundreds(Right(MyNumber, 2))
      If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
      If Count = 1 And Len(MyNumber) > 3 Then
      MyNumber = Left(MyNumber, Len(MyNumber) - 3)
      Else
      If Count > 1 And Len(MyNumber) > 2 Then
      MyNumber = Left(MyNumber, Len(MyNumber) - 2)
      Else
      MyNumber = ""
      End If
      End If
      Count = Count + 1
      Loop
      Select Case Rupees
      Case ""
      Rupees = "No Rupees"
      Case "One"
      Rupees = "One Rupee"
      Case Else
      '****************************************************************
      'Yogi Anand on 20-Sep-2003
      'modified the following two lines to display "Rupees" to precede
      ' rem'd the first line and added the second line
      '****************************************************************
      'Rupees = Rupees & " Rupees"
      Rupees = "Rupees " & Rupees

      End Select
      Select Case Paise
      Case ""
      '****************************************************************
      'Yogi Anand on 20-Sep-2003
      'modified the following two lines to display nothing for no paise
      ' rem'd the first line and added the second line
      '****************************************************************

      'Paise = " and No Paise"
      '****************************************************************
      'Yogi Anand on 03-Oct-2003
      'modified the following line to display " Only" for no paise
      ' rem'd the first line and added the second line
      '****************************************************************
      'Paise = ""
      Paise = " Only"
      Case "One"
      Paise = " and One Paisa"
      Case Else
      Paise = " and " & Paise & " Paise"

      End Select
      SpellIndian = Rupees & Paise
      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


      HTH
      Kris

      ExcelFox

      Comment


      • #4
        Re: Write a number in words

        The ASAP utilities can handle English, German and Dutch.

        www.asap-utilities.com
        Regards,

        Wigi

        Excel MVP 2011-2014

        For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

        -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

        Comment


        • #5
          Re: Write a number in words

          Hi VASUDEVAN

          Welcome to ozgrid

          See these custom conversion functions
          Convert a Numeric Value into English Words
          Convert a Currency or Value into English Words
          Last edited by Dave Hawley; January 29th, 2008, 10:03.

          Comment


          • #6
            Re: Write a number in words

            Dear Sir,
            The solutions given are very useful. But I really wanted some other solution. In Excel Worksheet, in A5 cell, there is a formula like =sum(A1:A4). For instance, that value is 1243. Then convert this number into English words as 'One Thousand Two Hundred and Forty Three'.

            Thanking you,
            VASUDEVAN,
            [email protected]

            Comment


            • #7
              Re: Write a number in words

              VASUDEVAN

              The code/links supplied will do that.

              Have you tried any of the suggestions?
              Boo!

              Comment


              • #8
                Re: Write a number in words

                Make sure you copy the code of Microsoft that Dave supplied you into a module into VBA. That code essentially is a function that the user - you! - learn to Excel. Its name is SpellNumber.

                Then, in your worksheet, you use:

                =SpellNumber(sum(A1:A4))

                in cell A5.

                Variations in terms of code, nesting functions, ... are of course possible.

                Wigi
                Regards,

                Wigi

                Excel MVP 2011-2014

                For more Excel memes: visit http://www.wimgielis.com ==> English articles ==> Excel memes

                -- Topics without [CODE] tags or a meaningful topic title, will be neglected by me (and probably many others as well) --

                Comment


                • #9
                  Re: Write a number in words

                  Can you help in enabling macros in the sheet.
                  As the function is working just fine, however whenever the file is edited the function breaks and gives error.
                  Also whenever i open the file freshly. The function breaks and no results are shown.

                  Error shown is #NAME?

                  Please help as im in process in developing a invoice and billing system for my small company.

                  Comment


                  • #10
                    Re: Write a number in words

                    Welcome to OzGrid, ElectroWorld.

                    The policy on this board is you do not post questions in threads started by other members.

                    Please start your own thread, give it an accurate and concise title that summarises your problem and explain your issue fully.

                    If you think this, or any other thread, can help clarify your issue you can include a link to it by copying the URL from the address bar of your browser and pasting into your message.

                    Comment


                    • #11
                      Re: Write a number in words

                      Dear Cytop,

                      I intend to get a solution for the string that was shared by
                      Krishnakumar

                      I faced a problem that i shared to get clarified and sought his help. I do not wish to start another thread and put burden on the forum with irrelevant topics.
                      Sir kindly see and solve my query as your string is asking for macros to be enabled.

                      Regards
                      ElectroWorld

                      Comment


                      • #12
                        Re: Write a number in words

                        ...I do not wish to start another thread
                        Your negative wish is not granted.

                        The board policy is you do not post questions in another members thread - regardless how relevant you might think it is.

                        Comment

                        Trending

                        Collapse

                        There are no results that meet this criteria.

                        Working...
                        X