Conversion of Excel Numerical figures into word names
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)
