06 July 2024
To convert numbers to words in Excel with the Indian Rupee symbol (₹), you can use a custom Excel add-in or a VBA (Visual Basic for Applications) script. Here's a simplified VBA code that you can use to achieve this functionality:
### VBA Code for Numbers to Words Conversion with Indian Rupee Symbol
1. **Open Excel and Press Alt + F11 to open the VBA Editor:**
This is where you will enter the VBA code.
2. **Insert a New Module:**
Right-click on any of the objects in the Project Explorer window (usually on the left side of the screen) and select `Insert` > `Module`. This will create a new module where you can paste the following VBA code.
3. **Paste the Following VBA Code:**
```vba Function ConvertNumberToWords(ByVal MyNumber As Double) As String Dim Temp As String Dim DecimalPlace As Integer Dim Count As Integer ReDim Place(9) As String Place(2) = " Thousand " Place(3) = " Million " Place(4) = " Billion " Place(5) = " Trillion "
' Convert MyNumber to English words MyNumber = Trim(Str(MyNumber))
' Position of decimal place 0 if none DecimalPlace = InStr(MyNumber, ".") If DecimalPlace > 0 Then Temp = 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 ConvertNumberToWords = Temp & Place(Count) & ConvertNumberToWords If Len(MyNumber) > 3 Then MyNumber = Left(MyNumber, Len(MyNumber) - 3) Else MyNumber = "" End If Count = Count + 1 Loop
Select Case Temp Case "" ConvertNumberToWords = "No Rupees" Case "One" ConvertNumberToWords = "One Rupee" Case Else ConvertNumberToWords = ConvertNumberToWords & " Rupees" End Select
If DecimalPlace > 0 Then ConvertNumberToWords = ConvertNumberToWords & " and " & Temp & " Paise" End If End Function
Private Function GetHundreds(ByVal MyNumber As String) As String 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
Private Function GetTens(TensText As String) As String Dim Result As String Result = "" ' Null out the temporary function value that gets returned. 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
Private Function GetDigit(Digit As String) As String 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 ```
4. **Save the Module:**
Save the module with an appropriate name. Close the VBA editor.
### Using the Function in Excel
Now that you have added the VBA code, you can use the `ConvertNumberToWords` function directly in your Excel sheet:
1. **In Excel, Enter a Number:**
In any cell, enter a numeric value you want to convert into words.
2. **Use the Function:**
In another cell, enter the formula `=ConvertNumberToWords(A1)` (assuming A1 contains the number you want to convert).
3. **Display with Indian Rupee Symbol:**
To display the result with the Indian Rupee symbol (₹), you can concatenate it with the symbol in the formula:
```excel ="₹ " & ConvertNumberToWords(A1) ```
### Notes:
- Make sure to format the cell containing the result as `Text` to preserve the formatting. - This VBA code converts the number into words including Rupees and Paise (Indian currency).
This method allows you to convert numbers to words with the Indian Rupee symbol directly in Excel using VBA. If you prefer an add-in approach, you may need to explore third-party Excel add-ins designed for this purpose, as creating custom add-ins requires additional development and installation steps.
CAclubindia
