Excel Function to Convert Rupee Amount to Text

Many time we need to get textual representation of Numerical Value. Many such functions are available through internet but for Indian system of Crore/Lakh/Hazar it is a bit rare.

Suppose you have 1234 and need it's textual representation. That is "One Thousand Two Hundred Thirty Four"

The function "TransferNumberToText" given below will help you in that case.

To know how to use custom function, you can refer
http://excelfeature.blogspot.in/2015/05/how-to-add-custom-function-in-excel.html

The limitations of this functions are :

It does not handle Negative Value or Decimal Value
It will not work for 100 Crore or more. (Just Kidding.. I am a poor person and this amount is too much for my function.)

.********************************************************************
Private Function TransferTen(numDasak As Byte) As String
    Select Case numDasak
        Case 1: TransferTen = "One"
        Case 2: TransferTen = "Two"
        Case 3: TransferTen = "Three"
        Case 4: TransferTen = "Four"
        Case 5: TransferTen = "Five"
        Case 6: TransferTen = "Six"
        Case 7: TransferTen = "Seven"
        Case 8: TransferTen = "Eight"
        Case 9: TransferTen = "Nine"
        Case Else: TransferTen = ""
    End Select
End Function
Private Function TenNineteen(num10_19 As Byte) As String
    Select Case num10_19
        Case 10: TenNineteen = "Ten"
        Case 11: TenNineteen = "Eleven"
        Case 12: TenNineteen = "Twelve"
        Case 13: TenNineteen = "Thirteen"
        Case 14: TenNineteen = "Fourteen"
        Case 15: TenNineteen = "Fifteen"
        Case 16: TenNineteen = "Sixteen"
        Case 17: TenNineteen = "Seventeen"
        Case 18: TenNineteen = "Eighteen"
        Case 19: TenNineteen = "Nineteen"
        Case Else: TenNineteen = ""
    End Select
End Function
Private Function TwentyNinety(Num20_90 As Byte) As String
    Select Case Num20_90
        Case 20: TwentyNinety = "Twenty"
        Case 30: TwentyNinety = "Thirty"
        Case 40: TwentyNinety = "Fourty"
        Case 50: TwentyNinety = "Fifty"
        Case 60: TwentyNinety = "Sixty"
        Case 70: TwentyNinety = "Seventy"
        Case 80: TwentyNinety = "Eighty"
        Case 90: TwentyNinety = "Ninety"
        Case Else: TwentyNinety = ""
    End Select
End Function
Private Function TransferHundred(NumSatak As Byte) As String
    Dim TempTH As String
   
    If NumSatak >= 10 And NumSatak <= 19 Then
        TransferHundred = TenNineteen(NumSatak)
    ElseIf NumSatak >= 0 And NumSatak <= 9 Then
        TransferHundred = TransferTen(NumSatak)
    ElseIf NumSatak >= 20 And NumSatak <= 99 Then
        If (NumSatak Mod 10) = 0 Then
            TempTH = TwentyNinety(Left$(NumSatak, 1) * 10)
        Else
            TempTH = TwentyNinety(Left$(NumSatak, 1) * 10) & " " & TransferTen(Right$(NumSatak, 1) * 1)
        End If
        TransferHundred = TempTH
    End If
End Function

Function TransferNumberToText(Num As Currency)
    Dim TempTNTT As String, tempText As String, Temp As Byte
   
    Num = Round(Num, 0)
   
    Select Case Num
        Case Is > 999999999
             TransferNumberToText = "Too Large Value"
            Exit Function
        Case 0
            TransferNumberToText = "Zero"
            Exit Function
        Case Is < 0
            TransferNumberToText = "Be Positive No Negative"
            Exit Function
    End Select
   
    TempTNTT = Num
   
    tempText = " Only"
   
    TempTNTT = Right$("00000000" & TempTNTT, 9)
   
    tempText = TransferHundred(CByte(Mid$(TempTNTT, 8, 2))) & tempText
   
    Temp = CByte(Mid$(TempTNTT, 7, 1))
    If Temp <> 0 Then
        tempText = TransferTen(Temp) & " Hundred " & tempText
    End If
   
    Temp = CByte(Mid$(TempTNTT, 5, 2))
    If Temp <> 0 Then
        tempText = TransferHundred(Temp) & " Thousand " & tempText
    End If
   
    Temp = CByte(Mid$(TempTNTT, 3, 2))
    If Temp <> 0 Then
        tempText = TransferHundred(Temp) & " Lakh " & tempText
    End If
   
    Temp = CByte(Mid$(TempTNTT, 1, 2))
    If Temp <> 0 Then
        tempText = TransferHundred(Temp) & " Crore " & tempText
    End If
   
    If Num = 1 Then
        TransferNumberToText = "Rupee " & tempText
    Else
        TransferNumberToText = "Rupees " & tempText
    End If
   
    TransferNumberToText = Application.WorksheetFunction.Trim(TransferNumberToText)
   
End Function

'********************************************************************************

How to Add Custom Function in Excel File - Small Tutorial

In Excel forum/group many items experts give very useful functions. But normally seen that many user is coming back telling that they do not know how to use that. In this tutorial I shall try to explain that.

Before following below steps it is better that you properly close all excel files and start.

Step 1 

Suppose the expert has given you below function. This is just sample. You can use actual function given by experts.

'******************************************************
Function TellMyAge(lYear As Long, lMonth As Long, lDay As Long)

    'Just for Example Purpose
    'Created by Asadulla Javed
    'This function shows your age in fraction

    TellMyAge = WorksheetFunction.YearFrac(DateSerial(lYear, _
        lMonth, lDay), Date)

End Function
'******************************************************

Step 2

a) Open excel. Create a Blank Excel workbook by pressing Ctrl+N or by clicking "New" from office menu.

You can use any old workbook also. But always try to keep all functions in single file if possible.

b) In the blank workbook press Alt+F11. This will show you VB Editor. Click Insert in Menu bar then click Module. Refer below pic.




c) Copy the Function code from browser. You have to copy from "Function Tell....." to "End Funct...."

Then paste in code window where cursor is blinking. See the right circle in pic.

 After pasting close the VB editor window.

Step 3

Return to Excel. Go to any blank cell. Either Press Shift+F3 or click Insert function icon at left of formula bar. The "Insert Function" dialog box will open. In the "or select a category" box, select "User Defined" . From next box "Select a Function"  select your function . Here it is "TellMyAge".

Now depending on function you have to give input. In this sample you have to give Year, Month and Day of your DOB one by one separating with comma.

If you are familiar enough with the function you can directly type in cell and give inputs.If you know all inputs and their type then go to blank cell, type =Tellmyage(2000,1,1) 

Step 4

Up to this point you can use function in only one excel workbook where you have pasted the code. Pls note that like worksheet, VB Codes are also attached with single workbook.

Normally if a function is very useful, you need to use in every file. But it is not possible to paste the code in all excel files.

For this step 4 is required.

a)  Click "Save As" from office menu or press F12. Excel "Save As" dialog will appear. Give a useful name to the file in "File name" box. Suppose you give name "Myfunction". In Bottom of dialog box you will find a box called "Save as type". Select "Excel Add-in" from that box. Press OK.

b) Click office menu. Click Options at bottom . Select "Add-In" . In bottom side you will find one List Box called "Manage". "Excel Add-in " is selected by default there. If it it is not selected then you select from list box. Then click "Go".

c) The "Add-ins" dialog will appear. Find the name you given to your file where formula pasted. Select it. Press OK. Exit excel.

Now as per step 3 you can check the function from any excel file.


Hope it helps and best wishes.....

Qicker way to find unique items than Autofilter

Excel Autofilter feature is one of most useful of this popular application. Whenever you click filter arrows (Or by shortcut Alt+Down Arrow ) excel shows the the items of that column. It contains each item once so you are confident that these are only available in this column

See the red eclipse below with corresponding list at left.

But if there is any cell which has more than 255 character then it will not be shown in autofilter. See pic below..

 

 We many time decide on basis Autofilter list. It is most of the time very effective but if cell has more than 255 characters then it will not appear in autofilter.

And another problem is if list is lengthy with many rows excel autofilter will take lot of time to prepare a unique list.

Some quick work arounds are...

One option is doing pivot of the list which will show only unique items.

Or copy the list to another place and use remove duplicate on the copy. It will remove all duplicates

Or you can use Excel's Advanced filter by checking "Unique Records". This is most simple way.Below is details..

First select list. Goto Data tab, then Advanced under "Sort & Filter" group. Check the dialog box. Most of the time excel guess correctly "List Range" but it may be wrong. Be sure that address shown in "List Range" is actually address of your list. Put Check on "Unique Records Only". Do nothing with 1st Option button group. Default is "Filter The List, in-place" and "Criteria Range". See the pic below..

The above will help in getting unique items of list in another scenario.

If any list contains more than 10000 unique items, autofilter will show only first 10000. Note: your list may be more than 10000 but it may contain less unique items. Suppose your list is simple arithmetic series of 1,2,3...upto 20000. Then autofilter will show you till 10000. And lot of time will pass before autofilter shows unique list to you.



Hope this helps...All the best wishes...