In this tutorial, you will learn how to convert numbers to Hindi words.
Using this Excel formula (VBA UDF) you can spell numbers in Indian rupees.
You can output numbers to Hindi words with and without rupees sign (₹).
Add spell number to Hindi word formula in Excel
Do the below steps to add formula to convert number to Hindi word in Excel.
- Click the below link and download the Excel file.
- Open the downloaded Excel file then save it as Excel Add-in.
- Go to the Developer tab and click on the Excel Add-ins option. In the add-in list tick on the file name of the add-in then click on OK.
- Now you are ready to use the f_spellNumHindi formula in the Excel cell.
Read in detail How to make Microsoft Excel Add-in?
Number to Hindi word formula syntax
Formula Syntax
f_spellNumHindi (number , [Currency Name] , [Currency Sign])
f_spellNumHindi function has 3 inputs. 1st input is mandatory and 2nd, 3rd inputs are optional.
number:- Mandatory. Range reference or any number.
[Currency Name]:- Optional (True/False) . True will display currency name & False will not.
[Currency Sign]:- Optional (True/False). True will display currency sign & False will not.
You can also write 1 for True and 0 for False.
VBA code to convert number to Hindi word
Below is the VBA code which is used to convert or spell numbers to Hindi words.
Don’t worry about code length, if you just read it carefully, it is simple and step-by-step process.
In this code section function named f_spellNumHindi is the VBA UDF (User Defined Function) which will be used in Excel cell as a formula to convert numbers to words.
Note:- VBA UDF functions must be written in the Standard module.
Read more about How to create Excel VBA User Defined Function
'source >> https://learnyouandme.com/
'written by >> Abhishek Pandey
Option Explicit
Private v_upto100SpellArr() As Variant
Private v_sectionSpellArr() As Variant
Private v_curUnitSpellArr() As Variant
Private v_rupeeSign As String
Private Sub setArrAndVals()
v_upto100SpellArr = Application.Transpose(Sheet1.Range("B1:B101"))
v_sectionSpellArr = Application.Transpose(Sheet1.Range("E1:E4"))
v_curUnitSpellArr = Application.Transpose(Sheet1.Range("H1:H2"))
v_rupeeSign = Sheet1.Range("J1").Value
End Sub
Private Function f_sectionInNumber(num As Long, secNum As Byte) As Long
Dim numStr As String
numStr = "000000000" & num
Select Case secNum
Case 1
f_sectionInNumber = Right(numStr, 3) * 1 '' hundred
Case 2
f_sectionInNumber = Left(Right(numStr, 5), 2) * 1 '' thousand
Case 3
f_sectionInNumber = Left(Right(numStr, 7), 2) * 1 ''lac
Case 4
f_sectionInNumber = Left(num, Len(CStr(num)) - 7) * 1 ''crore
End Select
End Function
Private Function f_Upto100Spell(num As Integer) As String
If num = 0 Then
f_Upto100Spell = ""
Exit Function
End If
f_Upto100Spell = v_upto100SpellArr(num + 1)
End Function
Private Function f_HunderedSpell(num As Integer) As String
Dim nm As Integer, secSpl As String
nm = Left(num, 1) * 1
If nm > 0 Then secSpl = v_sectionSpellArr(1) & " "
If num > 100 Then
f_HunderedSpell = f_Upto100Spell(nm) & " " & _
secSpl & _
f_Upto100Spell(Right(num, 2) * 1)
Else
f_HunderedSpell = f_Upto100Spell(num)
End If
End Function
Private Function f_ThousandSpell(num As Long) As String
Dim nm As Integer, secSpl As String
nm = f_sectionInNumber(num, 2)
If nm > 0 Then secSpl = v_sectionSpellArr(2) & " "
f_ThousandSpell = f_Upto100Spell(nm) & " " & _
secSpl & _
f_HunderedSpell(f_sectionInNumber(num, 1))
End Function
Private Function f_LacSpell(num As Long) As String
Dim nm As Integer, secSpl As String
nm = f_sectionInNumber(num, 3)
If nm > 0 Then secSpl = v_sectionSpellArr(3) & " "
f_LacSpell = f_Upto100Spell(nm) & " " & _
secSpl & _
f_ThousandSpell(Right(num, 5) * 1)
End Function
Private Function f_CroreSpell(num As Long) As String
Dim crNum As Integer, crSpl As String
crNum = f_sectionInNumber(num, 4)
If crNum <= 100 Then
crSpl = f_Upto100Spell(crNum)
Else
crSpl = f_HunderedSpell(crNum)
End If
f_CroreSpell = crSpl & " " & _
v_sectionSpellArr(4) & " " & _
f_LacSpell(Right(num, 7) * 1)
End Function
Private Function f_WholeNumSpell(num As Long) As String
Dim numLen As Byte
numLen = Len(CStr(num))
Select Case numLen
Case 1, 2, 3
f_WholeNumSpell = f_HunderedSpell(CInt(num))
Case 4, 5
f_WholeNumSpell = f_ThousandSpell(num)
Case 6, 7
f_WholeNumSpell = f_LacSpell(num)
Case Else
f_WholeNumSpell = f_CroreSpell(num)
End Select
End Function
Function f_spellNumHindi(num As Double, _
Optional curSpl As Boolean = False, Optional RsSign As Boolean = False) As String
If num = 0 Then
f_spellNumHindi = ""
Exit Function
End If
If v_rupeeSign = "" Then setArrAndVals
Dim wholeNum As Long
Dim fractionNum As Integer, deciPos As Byte
Dim u1 As String, u2 As String, rSgn As String
wholeNum = Fix(num)
deciPos = InStr(1, CStr(num), ".")
If deciPos = 0 Then
fractionNum = 0
Else
fractionNum = Mid(CStr(num) & "00", deciPos + 1, 2) * 1
End If
If curSpl Then
u1 = " " & v_curUnitSpellArr(1) & " "
u2 = " " & v_curUnitSpellArr(2)
End If
If RsSign Then rSgn = v_rupeeSign & " "
If wholeNum = 0 Then
f_spellNumHindi = rSgn & f_Upto100Spell(fractionNum) & u2
ElseIf fractionNum > 0 Then
f_spellNumHindi = rSgn & f_WholeNumSpell(wholeNum) & " " & u1 & f_Upto100Spell(fractionNum) & u2
Else
f_spellNumHindi = rSgn & f_WholeNumSpell(wholeNum) & u1
End If
f_spellNumHindi = Replace(f_spellNumHindi, " ", " ")
End Function
Working of formula and VBA code
This is not depth explanation but a generic or core idea behind this VBA UDF.
As you have come to know there is no inbuilt function to convert number to Hindi word in Excel. This can be done with the help of VBA UDF (User Defined Function).
In VBA code you can not write Hindi words. So in one sheet (“RefTbl” / code name Sheet1) I have mapped numbers and its Hindi word up to 100. I got these Hindi words online.
Hindi word for hundred, thousand, and so on.
Hindi word for Indian currency name also currency sign.
In another word, “RefTbl” worksheet is a helper sheet.
In VBA, I have referred “RefTbl” sheet Hindi words in a logical manner to output Hindi word of a given number.
The same method or technique can also be applied to convert numbers to other languages words like number to Marathi word, number to Bangle (taka) word, and number to Nepali word.
You just need to carefully change the language font of the respective number (number to word mapping) present in “RefTbl” worksheet (Sheet1 code name).
You may also need to change some VBA code logic as per the number pronunciation in your language.
You may also like to ready the below topics.
How to insert and run VBA code in Excel?
How to run macro in Excel|Create macro button
How to generate random numbers in Excel without duplicates using VBA