# How to Convert Number to Words in Excel in Rupees

Consider the following simple dataset that contains some numbers which we’ll turn into words in the column next to them.

### Step 1 – Using the Developer Tab

• Select the Developer tab.
• Click on the Visual Basic command.

### Step 2 – Create a New Module to Generate VBA Code

• The Visual Basic Application window will open.
• Select the Insert tab.
• Click on the Module option to create a new Module to write VBA code.

• Paste the following VBA code into the Module.
``````Function word(SNum As String)
'Declare the Variables
Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer
zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
word = ""
Exit Function
End If
zNumStr = Trim(Str(SNum))
If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0
If (zNumStr > 999999999.99) Then
word = "Digit excced Maximum limit"
Exit Function
End If
zDPInt = InStr(zNumStr, ".")
If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If
zP = 1
Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If
zStrTemp = ""
If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If
If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If
If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If
zP = zP + 1
Loop
If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = " Rupees " & zRStr
End If
If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If
word = zRStr & zRStr_Paisas & " Only"
End Function
Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String
If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
'Converts a Number from 100 to 999 into Word
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If
word_GetH = zRStr
End Function
Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String
'Converts a Number from 10 to 19 into Word

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
'Converts a Number from 20 to 99 into Word

zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""
If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If
word_GetT = zRStr
End Function
Function word_GetD(zDStr As String)
Dim zArr_1() As Variant
'Converts a Number from 1 to 9 into Word

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function
``````

### Step 3 – Using the Formulas Tab to Create User Defined Function

• Select the Formulas tab.
• Click on the Insert Function command.
• Select the User-Defined function from the Insert Function category list.
• Click OK.

Read More: How to Convert Peso Number to Words in Excel

### Step 4 – Applying the User-Defined Function to Convert Numbers to Words in Excel in Rupees

• You will get a window of the Word function.
• Select the first number from column B.
• Click OK.

• The first number is converted to words in rupees.

• Use the Fill Handle tool and drag it down from cell C5 to C12.

## Related Articles

<< Go Back to Spell Number in Excel | Convert Number to Text | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

1. Its showing only formula =word(k35), instead of text.
Pls help.

Bishawajit Chakraborty Oct 19, 2022 at 3:13 PM

Thank you, Dhroov, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

2. Wow! Thumbs up for your knowledge and effort for making it easy. Thanks a lot Bro. It worked for me

Bishawajit Chakraborty Nov 16, 2022 at 11:28 AM

Thank you so much Sharath for your response. If you have any questions, comments, or recommendations, kindly leave them in the comment section below.

Regards,

Bishawajit Chakraborty.

4. when i close file and next time it open that function not work ……all process repeat starting …..we type word(F28 ) that not work and written there Name like this

Bishawajit Chakraborty Nov 23, 2022 at 9:57 AM

Thank you, Madan, for your comment. In our Excel file, this formula is working. Can you please share your excel file with us? Email Address: [email protected]. We will try to solve your problem as soon as possible.

5. Thanks very much for this.
I have been able to work it out with small edits to suit my country’s currency.
How do I insert the word “and” in the formulae to read for example,
Instead of writing: “Rupees Six Hundred Forty Five only” or “Rupees Six thousand four hundred forty five thousand only”, etc, it will now read “Rupees Six hundred and forty five only” or “Rupees Six thousand four hundred and forty five thousand only”, etc ?

Bishawajit Chakraborty Feb 19, 2023 at 11:28 AM

Thank you, MEREDITH, for your wonderful question.

Here is our modified VBA code. Therefore, you can apply this code to solve your problem but you can also modified this code for three digit numbers by using word “and” in our code section.

Here is the image of modified code where we have added the word “and”.

``````Function word(SNum As String)
'Declare the Variables
Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer
zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
word = ""
Exit Function
End If
zNumStr = Trim(Str(SNum))
If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0
If (zNumStr > 999999999.99) Then
word = "Digit excced Maximum limit"
Exit Function
End If
zDPInt = InStr(zNumStr, ".")
If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If
zP = 1
Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If
zStrTemp = ""
If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If
If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If
If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If
zP = zP + 1
Loop
If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = " Rupees " & zRStr
End If
If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If
word = zRStr & zRStr_Paisas & " Only"
End Function
Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String
If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
'Converts a Number from 100 to 999 into Word
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = " and " & word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If
word_GetH = zRStr
End Function
Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String
'Converts a Number from 10 to 19 into Word

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
'Converts a Number from 20 to 99 into Word

zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""
If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If
word_GetT = zRStr
End Function
Function word_GetD(zDStr As String)
Dim zArr_1() As Variant
'Converts a Number from 1 to 9 into Word

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function``````

Here, you will see the final result.

I hope this may solve your issue.

Bishawajit, on behalf of ExcelDemy

• Thank you for this wonderful code. It is really nice and useful. However, there is a small glitch – “and” is in wrong place. For example, “22,44,556” expresses as “Rupees Twenty Two Lacs Forty Four Thousand and Five Hundred Fifty Six Only”, but it should be “Rupees Twenty Two Lacs Forty Four Thousand Five Hundred and Fifty Six Only”. Also, there is an extra blank on each side of “and”. Can you please rectify this minor issue? Otherwise your code is excellent!

6. Thankyou so much. Since long was trying but finally found the solution

Syed Mohisn Ahmed Feb 14, 2024 at 1:00 PM

I want to use this formula for preparing checks , and in our banking systems we can use the word “rupees” in starting , can you please modified the coding and reply me as soon as possible

E.g : One Hundred Seventy Five Thousand Only which is (175,000/-) in numbers.

Please response as soon as possible

• Hello Syed Mohisn Ahmed,

Updated the code in the marked section. Where I replaced “Rupees” text from condition.

You can use the following code.

``````Function word(SNum As String)
'Declare the Variables
Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer
zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")
On Error Resume Next
If SNum = "" Then
word = ""
Exit Function
End If
zNumStr = Trim(Str(SNum))
If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0
If (zNumStr > 999999999.99) Then
word = "Digit excced Maximum limit"
Exit Function
End If
zDPInt = InStr(zNumStr, ".")
If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(xNumStr) - xDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If
zP = 1
Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If
zStrTemp = ""
If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(xStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If
If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If
If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If
zP = zP + 1
Loop
If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = " Rupees " & zRStr
End If
If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If
word = zRStr & zRStr_Paisas & " Only"
End Function
Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String
If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
'Converts a Number from 100 to 999 into Word
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If
If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If
word_GetH = zRStr
End Function
Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String
'Converts a Number from 10 to 19 into Word

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
'Converts a Number from 20 to 99 into Word

zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""
If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If
word_GetT = zRStr
End Function
Function word_GetD(zDStr As String)
Dim zArr_1() As Variant
'Converts a Number from 1 to 9 into Word

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")
If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function
``````

Regards
ExcelDemy

8. Hi ,

How to use this for 2 Decimals

Lutfor Rahman Shimanto Mar 14, 2024 at 4:33 PM

Hello CHANDRA

Thanks for visiting our blog and posting an exciting comment. You want to convert decimal numbers (up to two decimal places) to words in Rupees.

1. Right-click on the sheet name tab.
2. Click on View Code.
3. Now, please insert the following code in the sheet module and save it:
``````Function AdvancedWord(SNum As String)

Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer

zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")

On Error Resume Next

If SNum = "" Then
word = ""
Exit Function
End If

zNumStr = Trim(Str(SNum))

If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0

If (zNumStr > 999999999.99) Then
word = "Digit exceeds Maximum limit"
Exit Function
End If

zDPInt = InStr(zNumStr, ".")

If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(zNumStr) - zDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If

zP = 1

Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If

zStrTemp = ""

If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(zStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If

If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If

If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If

zP = zP + 1
Loop

If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = " Rupees " & zRStr
End If

If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If

AdvancedWord = zRStr & zRStr_Paisas & " Only"
End Function

Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String

If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If

If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If

word_GetH = zRStr
End Function

Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""

If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If

word_GetT = zRStr
End Function

Function word_GetD(zDStr As String)
Dim zArr_1() As Variant

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")

If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function``````

5. Apply the following formula: `=AdvancedWord(B5)`
6. Drag the Fill Handle icon to copy the formula down.

I am also attaching the solution workbook for better understanding. I hope the solution will help you; good luck.

Regards

Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy

• HELLO LUTFOR RAHMAN SHIMANTO.
COULD YOU PLEASE MAKE THIS FORMULA TO QATAR RIYAL?

• Lutfor Rahman Shimanto May 9, 2024 at 11:32 AM

Dear Abinsh

Thanks for sharing your requirements. You want to convert numbers to words in Qatar Riyal. Don’t worry! I have modified the previously given code to fulfil your goal.

Excel VBA User-Defined Function:

``````Function AdvancedWord(SNum As String)

Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String
Dim zBp As Integer

zArrPlace = Array("", "", " Thousand ", " Lacs ", " Crores ", " Trillion ", "", "", "", "")

On Error Resume Next

If SNum = "" Then
word = ""
Exit Function
End If

zNumStr = Trim(Str(SNum))

If zNumStr = "" Then
word = ""
Exit Function
End If

zRStr = ""
zBp = 0

If (zNumStr > 999999999.99) Then
word = "Digit exceeds Maximum limit"
Exit Function
End If

zDPInt = InStr(zNumStr, ".")

If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(zNumStr) - zDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If

zP = 1

Do While zNumStr <> ""
If (zP >= 2) Then
zTemp = Right(zNumStr, 2)
Else
If (Len(zNumStr) = 2) Then
zTemp = Right(zNumStr, 2)
ElseIf (Len(zNumStr) = 1) Then
zTemp = Right(zNumStr, 1)
Else
zTemp = Right(zNumStr, 3)
End If
End If

zStrTemp = ""

If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zBp)
If Right(Trim(zStrTemp), 3) <> "Lac" Then
zBp = zBp + 1
End If
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If

If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If

If zP = 2 Then
If Len(zNumStr) = 1 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
ElseIf zP = 3 Then
If Len(zNumStr) >= 3 Then
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
Else
zNumStr = ""
End If
ElseIf zP = 4 Then
zNumStr = ""
Else
If Len(zNumStr) <= 2 Then
zNumStr = ""
Else
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
End If
End If

zP = zP + 1
Loop

If zRStr = "" Then
zRStr = "No Qatar Riyals"
Else
zRStr = " Qatar Riyals " & zRStr
End If

If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Dirhams"
End If

AdvancedWord = zRStr & zRStr_Paisas & " Only"
End Function

Function word_GetH(zStrH As String, zBp As Integer)
Dim zRStr As String

If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
If (zBp > 0) Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Lac "
Else
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If
End If

If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If

word_GetH = zRStr
End Function

Function word_GetT(zTStr As String)
Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
Result = ""

If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If

word_GetT = zRStr
End Function

Function word_GetD(zDStr As String)
Dim zArr_1() As Variant

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")

If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If
End Function
``````

I hope you have found the solution you were looking for. I have attached the solution workbook. Good luck.

Regards

Lutfor Rahman Shimanto

Excel & VBA Developer

ExcelDemy

9. When i’m entering more the 100 crores value its showing digits exceeds maximum limit, How to rectify the error.

• Hello Praveen

As we used digit limit in our existing code that’s why it is showing this warning.

Don’t worry! We have updated the existing VBA user-defined function to overcome the problem and work with much larger numbers. In the Indian numbering system, we use terms such as Thousand, Lakh, Crore, Arab, Kharab, Neel, Padma, and Shankh to express large numbers. So, the user-defined function will return the word-converted result using these terms. Moreover, we will use an Excel built-in TEXT function to get accurate results for huge numbers.

Use the following updated code:

``````Function AdvancedWord(SNum As String) As String

Dim zDPInt As Integer
Dim zArrPlace As Variant
Dim zRStr_Paisas As String
Dim zNumStr As String
Dim zP As Integer
Dim zTemp As String
Dim zStrTemp As String
Dim zRStr As String

zArrPlace = Array("", " Thousand ", " Lakh ", " Crore ", " Arab ", " Kharab ", " Neel ", " Padma ", " Shankh ")

If SNum = "" Then
Exit Function
End If

zNumStr = Trim(CStr(SNum))

If zNumStr = "" Then
Exit Function
End If

zRStr = ""

If (Val(zNumStr) >= 1E+19) Then
AdvancedWord = "Digit exceeds Maximum limit"
Exit Function
End If

zDPInt = InStr(zNumStr, ".")

If zDPInt > 0 Then
If (Len(zNumStr) - zDPInt) = 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1) & "0", 2))
ElseIf (Len(zNumStr) - zDPInt) > 1 Then
zRStr_Paisas = word_GetT(Left(Mid(zNumStr, zDPInt + 1), 2))
End If
zNumStr = Trim(Left(zNumStr, zDPInt - 1))
End If

If InStr(zNumStr, "E") > 0 Then
zNumStr = CDec(zNumStr)
End If

zP = 0

Do While zNumStr <> ""
If Len(zNumStr) > 2 Then
If zP = 0 Then
zTemp = Right(zNumStr, 3)
zNumStr = Left(zNumStr, Len(zNumStr) - 3)
Else
zTemp = Right(zNumStr, 2)
zNumStr = Left(zNumStr, Len(zNumStr) - 2)
End If
Else
zTemp = zNumStr
zNumStr = ""
End If

zStrTemp = ""

If Val(zTemp) > 99 Then
zStrTemp = word_GetH(Right(zTemp, 3), zP)
ElseIf Val(zTemp) <= 99 And Val(zTemp) > 9 Then
zStrTemp = word_GetT(Right(zTemp, 2))
ElseIf Val(zTemp) < 10 Then
zStrTemp = word_GetD(Right(zTemp, 2))
End If

If zStrTemp <> "" Then
zRStr = zStrTemp & zArrPlace(zP) & zRStr
End If

zP = zP + 1
Loop

If zRStr = "" Then
zRStr = "No Rupees"
Else
zRStr = "Rupees " & zRStr
End If

If zRStr_Paisas <> "" Then
zRStr_Paisas = " and " & zRStr_Paisas & " Paisas"
End If

AdvancedWord = zRStr & zRStr_Paisas & " Only"

End Function

Function word_GetH(zStrH As String, zP As Integer) As String

Dim zRStr As String

If Val(zStrH) < 1 Then
word_GetH = ""
Exit Function
Else
zStrH = Right("000" & zStrH, 3)
If Mid(zStrH, 1, 1) <> "0" Then
zRStr = word_GetD(Mid(zStrH, 1, 1)) & " Hundred "
End If

If Mid(zStrH, 2, 1) <> "0" Then
zRStr = zRStr & word_GetT(Mid(zStrH, 2))
Else
zRStr = zRStr & word_GetD(Mid(zStrH, 3))
End If
End If

word_GetH = zRStr

End Function

Function word_GetT(zTStr As String) As String

Dim zTArr1 As Variant
Dim zTArr2 As Variant
Dim zRStr As String

zTArr1 = Array("Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen")
zTArr2 = Array("", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")

If Val(Left(zTStr, 1)) = 1 Then
zRStr = zTArr1(Val(Mid(zTStr, 2, 1)))
Else
If Val(Left(zTStr, 1)) > 0 Then
zRStr = zTArr2(Val(Left(zTStr, 1)) - 1)
End If
zRStr = zRStr & word_GetD(Right(zTStr, 1))
End If

word_GetT = zRStr

End Function

Function word_GetD(zDStr As String) As String

Dim zArr_1 As Variant

zArr_1 = Array(" One", " Two", " Three", " Four", " Five", " Six", " Seven", " Eight", " Nine", "")

If Val(zDStr) > 0 Then
word_GetD = zArr_1(Val(zDStr) - 1)
Else
word_GetD = ""
End If

End Function``````

• Apply the following formula: `=AdvancedWord(TEXT(A1,"#.00"))`
• Drag the Fill Handle icon to copy the formula down.
• Hopefully, you have found the solution you were looking for. I have attached the solution workbook as well. Good luck.