EASYOFFICE
EASYOFFICE
EASYOFFICE

CONVERT NUMBERS TO WORDS IN EXCEL

This query is : Resolved 

20 March 2024 Sir,

Please share Suresh Add on software in excel to convert Numbers to Words in excel with Indian
Rupee Symbol. Advance Thanks

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.



You need to be the querist or approved CAclub expert to take part in this query .
Click here to login now

Join CCI Pro
CAclubindia's WhatsApp Groups Link


Similar Resolved Queries


loading


Unanswered Queries