Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 8 of 8

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,254

    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
    5,887

    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,697

    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,539

    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
    5,887

    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


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