Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 12

Thread: Write a number in words

  1. #1
    Join Date
    5th February 2006
    Posts
    4

    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
    vasudevanvelayudhan@yahoo.com

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Greychild Guest

    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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,424

    Re: Write a number in words

    Hi Vasu,

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

    VB:
    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

  4. #4
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,069

    Re: Write a number in words

    The ASAP utilities can handle English, German and Dutch.

    www.asap-utilities.com

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,744

    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 at 09:03.

  6. #6
    Join Date
    5th February 2006
    Posts
    4

    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,
    vasudevanvelayudhan@yahoo.com

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th July 2004
    Posts
    10,542

    Re: Write a number in words

    VASUDEVAN

    The code/links supplied will do that.

    Have you tried any of the suggestions?

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    15th August 2005
    Location
    Mechelen, Belgium
    Posts
    6,069

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    19th August 2015
    Location
    India
    Posts
    2

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    1st September 2010
    Posts
    9,338

    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.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Rounded Number In Words
    By mlr0911 in forum EXCEL HELP
    Replies: 2
    Last Post: January 28th, 2008, 23:28

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
  •  
porno