Conversion of Excel Numerical figures into word names

Asked By 7060 points N/A Posted on -
qa-featured

I have maintained the employees data in MS-Excel speared sheet. I want to convert numerical data into word names.

The data is as under:

Pay for Month of January/2012        Rs.8,999,60

Pay for Month of February/2012       Rs.8,999,60

Pay for Month of March/2012            Rs.8,999,60

Pay for Month of April/2012              Rs.8,999,60

Pay for Month of May/2012               Rs.8,999,60

Total Pay Rs.4499800 (Rs.Four million Four Hundred Ninety Nine Thousand Eight Hundred)

Now I want to write the numerical data of Rs.4499800 into word names as under:

Four million Four Hundred Ninety Nine Thousand Eight Hundred.

So how the numerical data in converted into word names?

SHARE
Best Answer by hasnatdastgir
Go To Solution
Answered By 0 points N/A #83562

Conversion of Excel Numerical figures into word names

qa-featured

Many times we'd like the number in figures to be converted into words. this is often a typical demand for writing checks or the other monetary reports. Microsoft Excel doesn't have normal perform out there for this demand. but there are customized functions out there on the net.

Convert Text to Numbers using the VALUE Function
Sometimes once you import knowledge into Excel from another program or from the net, Excel mixes up its                                            
knowledge sorts and sees numerical knowledge as text knowledge.
When this case happens, bound functions, like add or AVERAGE, ignore the information in these cells and you finish
up with calculation errors.

In this tutorial we are going to use the worth perform to convert numbers entered as text knowledge to values and
then add them up using the add perform.

The Default Alignment of information in Excel

The alignment {of knowledge|of knowledge|of information} during a cell is a technique to spot whether or not data
has been entered as text or values. By default text knowledge aligns to the left during a cell and values on the
correct.

In the image on top of, the numbers in D1 and D2 align on the left aspect of the cell as a result of they need been
entered as text knowledge.

In cells E1 and E2 the information has been converted to values and thus aligns to the correct.

The VALUE Function's Syntax and Arguments

A perform's syntax is all about the function layout which involves the function name, brackets and arguments.


The syntax for the worth perform is:= value ( Text )

Text – the information you wish to convert. The argument will contain the particular knowledge enclosed in quotation
marks or it are often a cell reference to the situation of the text knowledge within the worksheet.
Example: Convert Text to Numbers with the worth perform
Entering Numbers as Text
1.  Enter the information into following cells – note that typing an apostrophe ( ' ) in front of variety
    turns it into text data.

Cell knowledge
D1  –  '10
D2  –  '20

2.  Type =SUM(D1 : D2) into cell D3 and press the Enter key on the keyboard.
3.  An answer of zero ought to seem in cell D3. Since {the knowledge|the info|the information} in cells D1 and D2 is text data it's ignored by the add perform.


Converting the Text knowledge to Numbers with the worth perform

1.  Click on cell E1 within the worksheet.
2.  Click the Formulas tab of the ribbon menu.
3.  Choose Text from the ribbon to open the perform drop down list.
4.  Click on price within the list to say the function's dialog box.
5.  In the dialog box, click on the Text line.
6.  Click on cell D1 within the spreadsheet.
7.  Click OK.
8.  The number ten ought to seem in cell E1.It ought to be aligned on the correct aspect of the cell indicating it's currently a price that may be employed in calculations.
9.  Use the fill handle to repeat the worth perform in cell E1 to cell E2.
10. The number twenty ought to seem in cell E2 and be aligned on the correct aspect of the cell.
11. Type =SUM(E1 : E2) into cell E3 and press the Enter key on the keyboard.
12. An answer of thirty ought to seem in cell E3.
13. Since the information in cells E1 and E2 has been converted to numbers they're currently included within the add function's calculations.
14. When you click on cell E1 the whole perform = price ( D1 ) seems within the formula bar on top of the worksheet.
About Stephanie Clar
Questions
0
Answers
53
Best Answers
26
  • Posted on -
  • Question Category:
Best Answer
Best Answer
Answered By 20 points N/A #83563

Conversion of Excel Numerical figures into word names

qa-featured

The Question for how to convert numerical data into word names in an Excel Spreadsheet is given hereunder:

How to create the sample function Called SpellNumber

1.        Start Microsoft Excel.

2.        Press ALT+F11 to start the Visual Basic Editor.

3.        On the Insert menu, click Module.

4.        Type the following code into the module sheet.

NOTED: IF any body desires to change currency, then he may modify the currency as per country in the following module as follows:

FOR EXAMPLE; In Pakistan, currency is used as  'Rupees' and 'Paisas'

For 'Dollars' , you may modify it as 'Rupees'

For 'Dollar' , you may modify it as 'Rupee'

For 'Cents' , you may modify it as 'Cents'

For 'Cent' , you may modify it as 'Cent'

Option Explicit

'Main Function

Function SpellNumber(ByVal MyNumber)

    Dim Rupees, Paisas, 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 Paisas and set MyNumber to Rupee amount.

    If DecimalPlace > 0 Then

    Paisas = 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 Rupees = Temp & Place(Count) & Rupees

        If Len(MyNumber) > 3 Then

        MyNumber = Left(MyNumber, Len(MyNumber) – 3)

        Else

        MyNumber = ""

        End If

        Count = Count + 1

    Loop

    Select Case Rupees

    Case ""

    Rupees = "No Rupees"

    Case "One"

     Rupees = "One Rupee"

     Case Else

      Rupees = Rupees & " Rupees"

    End Select

    Select Case Paisas

        Case ""

            Paisas = " and No Paisas"

        Case "One"

            Paisas = " and One Paisa"

              Case Else

            Paisas = " and " & Paisas & " Paisas"

    End Select

    SpellNumber = Rupees & Paisas

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
                              

How to use the SpellNumber sample function

To use the sample functions to change a number to written text, use one of the methods demonstrated in the following examples:

Method 1: Direct Entry

You can change 32.50 into "Thirty Two Rupees and Fifty Paisas" by entering the following formula into a cell: =SpellNumber(32.50)

Method 2: Cell reference

You can refer to other cells in the workbook. For example, enter the number 32.50 into cell A1, and type the following formula into another cell:

=SpellNumber(A1)

About hasnatdastgir
Questions
4
Answers
1
Best Answers
1
  • Posted on -
  • Question Category:

How do I make my hidden folders visible?

Previous

Release of Samsung Galaxy S3

Next

Related Questions