Excel/Excel formola

Advertisement


Question
hello sir,
please give me the formula for excel sheet to convert numeric figure into words.
e.g. 18,123.25 >in cell H36.
i want answer in cell I36 > Eighteen Thousand One Hundred Twenty Three Rupees & Twenty Five Peisa Only.

Answer
Microsoft has a user-defined function (UDF) you can install in your workbook to provide a new function to accomplish this, it is called SpellNumber.  
http://support.microsoft.com/kb/213360

Here is the code, I changed the output word "dollars" to "rupees" and cents to "peisas":

---------------
Option Explicit
'Main Function
Function SpellNumber(ByVal MyNumber)
   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 Rupees"
       Case "One"
         Dollars = "One Rupee"
        Case Else
         Dollars = Dollars & " Rupees"
   End Select
   Select Case Cents
       Case ""
         Cents = " and No Peisas"
       Case "One"
         Cents = " and One Peisa"
         Case Else
         Cents = " and " & Cents & " Peisas"
   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
--------------------

How to install the UDF into your workbook:

1. Press ALT+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module.
3. Paste the code given above into the new module
4. ALT+F11 will close the VB editor

How to use the new function.

1. Assuming your cell A1 has the number in it
2. In another cell enter this new function formula:

=SpellNumber(A1)  
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Jerry Beaucaire

Expertise

Excel Formulas, macros, automation. Microsoft Excel MVP - 2010. Code site with free code snippets and techniques: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files

Experience

Microsoft Excel MVP - 2010. I have my own extensive Excel help/code site: http://sites.madrocketscientist.com/jerrybeaucaires-excelassistant/files ===================== I have been offering free assistance as an Excel aid on many web sites for many years: (http://www.excelforum.com - JBeaucaire) ======== (http://www.askmehelpdesk.com/spreadsheets - JBeaucaire) ======= (http://www.mrexcel.com/forum - jbeaucaire)

Education/Credentials
Bachelor's Degree from Azusa Pacific University in Mathematics and Music Composition

Awards and Honors
Microsoft Excel MVP 2010

©2016 About.com. All rights reserved.