In banking or accountancy, this is used to write grand total amounts. Amounts are mentioned in Numbers as well as words. You probably have seen it on invoices or on Bank Cheques.
We can write the above number in words in many ways in different languages, countries Number scales, etc. Here are the two, for which, I have adjusted the function.
In American English: Six Million Seven Hundred Thirty-Four Thousand Eight Hundred and Sixty Two.
In Indian continent Number Scale : Sixty Seven Lakhs Thirty Four Thousand Eight Hundred and Sixty Two.
As we do not have any built in function as such in Microsoft Excel yet – therefore, I have created this User Defined function (UDF) to convert Numbers to Letters. You can also download a FREE Excel Workbook with this UDF in it.
Though there was a VBA code available Microsoft office wiki about spelling currency in words. I, too had written an article previously about converting Indian currency in words.
Limitations in Previous UDF
For example: if you want the maximum scale unit to be used to spell your amount is Billion (like Thousand, Million, and Billion) and you provide an amount which is (more than 999 Billion) 1000 Billion or more. Then, in this case, these functions were not capable of dealing with that and returns the strange result.
To overcome above limitation, I have created a function in VBA using recursive method to convert Numbers to letters. That means in the above example, this function will be able to give you correct spelling for 1000 Billion or even more.
For Example One Thousand Billion or Ten Thousand Billion or Hundred Thousand Billion or One Million Billion etc.
This is possible because of usingrecursive function . In simple language – if a function calls itself from its own body the function is called recursive function and this method is called recursion.
Since this is User Defined Function to convert numbers to words or in other words, spell numbers in Words. – You can use it as a formula in any cell in your excel workbook.
If you do not know what is UDF – User Defined Function – Refer this article.
To make it re-usable and simplify my previous code to convert numbers to letters, I have made a logical split of the whole code. I have made it in a more modular way which will be a lot easier to understand and change according to your need.
1# VBA function to Spell Numbers to Letters
1. Below is the VBA function [recursive function] to spell numbers to letters. It does not care about the currency Unit or sub-Unit etc. This is simply used to convert your number in words. The benefit of creating this as a separate function is that you can use this function to convert any type of Numbers to Letters. It is not necessary that you should use this for converting a currency amount to words only.
Most importantly, this function accepts whole number as an input. It does not accept decimal numbers. Therefore, if you want to convert a decimal number into words, you can split whole and decimal numbers and call this function separately. This is what I have done in SpellCurrency functions below. You can refer to them below.
Function NumToLettersX(ByVal num As String, _
Optional NumLetters As String = "", _
Optional UnitCounter As Integer = 0) As String
' Add the unit until where you want to use
' in Number Scale - Like
' Arab, Kharab, etc. [in Indian Continent Number Scale System]
Dim Unit As Variant
Unit = Array( _
"", _
" Thousand ", _
" Lakhs ", _
" Crore ")
Dim iCount As Integer: iCount = 1
Do While num <> ""
' Check if you have reached at the last Unit
' defined in the array - Unit
' if yes, call this same function by providing
' the remaining digits
If (iCount = UBound(Unit) + 1 And Len(num) > 2) Then
NumLetters = Unit(UnitCounter) & NumLetters
NumToLettersX num, NumLetters
Exit Do
End If
If (iCount = 1) Then
Temp = GetHundreds(Right(num, 3))
Else
Temp = GetHundreds(Right(num, 2))
End If
If (Temp <> "Zero") Then NumLetters = Temp & Unit(UnitCounter) & NumLetters
If Len(num) > 2 And iCount = 1 Then
num = Left(num, Len(num) - 3)
ElseIf Len(num) >= 2 And iCount > 1 Then
num = Left(num, Len(num) - 2)
Else
num = ""
End If
iCount = iCount + 1
UnitCounter = UnitCounter + 1
Loop
NumToLettersX = Application.WorksheetFunction.Trim(NumLetters)
End Function
Important to note that the above code is for the Indian continent Number Scale System Only. You can customize it according to your need.
Example: In Indian Continent Number Scale System: 6734862: Sixty Seven Lakhs Thirty-Four Thousand Eight Hundred and Sixty Two.
I have customized the above function for American Number Scale system . Following is the code for the American Number Scale System:
Function NumToLettersY(ByVal num As String, _
Optional NumLetters As String = "", _
Optional UnitCounter As Integer = 0) As String
' Add the unit until where you want to use
' in Number Scale - Like
' Million, Billion etc. [in American Number Scale System]
Dim Unit As Variant
Unit = Array( _
"", _
" Thousand ", _
" Million ", _
" Billion ", _
" Trillion ")
Dim iCount As Integer: iCount = 1
Do While num <> ""
' Check if you have reached at the last Unit
' defined in the array - Unit
' if yes, call this same function by providing
' the remaining digits
If (iCount = UBound(Unit) + 1 And Len(num) > 3) Then
NumLetters = Unit(UnitCounter) & NumLetters
NumToLettersY num, NumLetters
Exit Do
End If
Temp = GetHundreds(Right(num, 3))
If (Temp <> "Zero") Then NumLetters = Temp & Unit(UnitCounter) & NumLetters
If Len(num) > 3 Then
num = Left(num, Len(num) - 3)
Else
num = ""
End If
iCount = iCount + 1
UnitCounter = UnitCounter + 1
Loop
NumToLettersY = Application.WorksheetFunction.Trim(NumLetters)
End Function
Example: In American Number Scale System: 6734862: Six Million Seven Hundred Thirty-Four Thousand Eight Hundred and Sixty Two.
Most Important:
For above function to work correctly, DO NOT FORGET to copy the generic functions below. Spell Numbers to Letters functions uses these generic functions.
2# VBA function to Spell Currency to Words – American Number Scale System
1. Round-Up the input number provided to Spell the currency up to two-digit.
2. Separate the whole number part and decimal part from the input [Note: This is because above function accepts only whole number as input]
3. Call the Spell Number function for American Number Scale System to spell the numbers separately – 1 for the Whole number and one for the Decimal part(rounded to two digits)
4. In both converted parts, append the currency Unit and Sub-Unit, etc. in the returned Number spellings for Whole Number and Decimal part respectively.
Note: Likewise you can use this method for any other types of Numbers like Weight, Height, or any kind of Units and Sub-Units.
Function spellCurrencyY(ByVal MyNumber) As String
Dim wholeNumber As String
Dim decimalNumber As String
Dim wholeCurrencyText As String
Dim decimalCurrencyText As String
Dim unitSingular As String
Dim subUnitSingular As String
Dim unitPlural As String
Dim subUnitPlural As String
unitSingular = " Dollar"
unitPlural = " Dollars"
subUnitSingular = " Cent"
subUnitPlural = " Cents"
' separate whole number part and decimal part
' Note: for decimal points, first round up to 2 digits
MyNumber = VBA.Format(Round(MyNumber, 2), "#0.00")
If (VBA.InStr(1, MyNumber, ".", vbTextCompare) > 0) Then
wholeNumber = VBA.Left(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) - 1)
decimalNumber = VBA.Mid(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) + 1, 2)
Else
wholeNumber = MyNumber
decimalNumber = "0"
End If
' get the number spelling separately
wholeCurrencyText = NumToLettersY(wholeNumber)
decimalCurrencyText = NumToLettersY(decimalNumber)
Select Case wholeCurrencyText
Case "One"
wholeCurrencyText = wholeCurrencyText & unitSingular
Case ""
wholeCurrencyText = "Zero" & unitSingular
Case Else
wholeCurrencyText = wholeCurrencyText & unitPlural
End Select
Select Case decimalCurrencyText
Case "One"
decimalCurrencyText = decimalCurrencyText & subUnitSingular
Case ""
decimalCurrencyText = "Zero" & subUnitSingular
Case Else
decimalCurrencyText = decimalCurrencyText & subUnitPlural
End Select
spellCurrencyY = wholeCurrencyText & " and " & decimalCurrencyText
End Function
In American Number Scale System : 6734862.456 : Six Million Seven Hundred Thirty Four Thousand Eight Hundred and Sixty Two Dollars and Fourty Six Cents.
3# VBA function to Spell Currency to Words – Indian Continent Number Scale System
Basically, this function works on same principal as above. The only change from above function is that – you should call corresponding function for Indian Number Scale system.
Function spellCurrencyX(ByVal MyNumber) As String
Dim wholeNumber As String
Dim decimalNumber As String
Dim wholeCurrencyText As String
Dim decimalCurrencyText As String
Dim unitSingular As String
Dim subUnitSingular As String
Dim unitPlural As String
Dim subUnitPlural As String
unitSingular = " Rupee"
unitPlural = " Rupees"
subUnitSingular = " Paisa"
subUnitPlural = " Paise"
' separate whole number part and decimal part
' Note: for decimal points, first round up to 2 digits
MyNumber = VBA.Format(Round(MyNumber, 2), "#0.00")
If (VBA.InStr(1, MyNumber, ".", vbTextCompare) > 0) Then
wholeNumber = VBA.Left(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) - 1)
decimalNumber = VBA.Mid(MyNumber, VBA.InStr(1, MyNumber, ".", vbTextCompare) + 1, 2)
Else
wholeNumber = MyNumber
decimalNumber = "0"
End If
' get the number spelling separately
wholeCurrencyText = NumToLettersX(wholeNumber)
decimalCurrencyText = NumToLettersX(decimalNumber)
' get the
Select Case wholeCurrencyText
Case "One"
wholeCurrencyText = wholeCurrencyText & unitSingular
Case ""
wholeCurrencyText = "Zero" & unitSingular
Case Else
wholeCurrencyText = wholeCurrencyText & unitPlural
End Select
Select Case decimalCurrencyText
Case "One"
decimalCurrencyText = decimalCurrencyText & subUnitSingular
Case ""
decimalCurrencyText = "Zero" & subUnitSingular
Case Else
decimalCurrencyText = decimalCurrencyText & subUnitPlural
End Select
spellCurrencyX = wholeCurrencyText & " and " & decimalCurrencyText
End Function
In Indian Continent Number Scale System : 6734862.456 : Sixty Seven Lakhs Thirty Four Thousand Eight Hundred Sixty Two Rupees and Fourty Six Paise.
4# Generic Functions [Common for all the above functions]
Following are the generic functions. We use these functions to translate single digits, tens and hundreds in corresponding numbers system.
In below example, I have used English translation for that.
Similarly, if you want to change them in any other language, you can easily change the corresponding Text part for each Numbers.
'*******************************************
' Converts a number from 100-999 into text *
'*******************************************
Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then GetHundreds = "Zero": 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
'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)
Dim Result As String
Result = "" ' Null out the temporary function value.
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
'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************
Function GetDigit(Digit)
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
How to use these above Functions in Excel
Follow the following 5 simple steps to use this UDF.
Step 1. Open your Excel Workbook
Step 2. Press Alt + F11
Step 3. Add a Regular Module in Excel
Step 4. Copy and Paste the Below Code in the Module or in separate modules [your wish]
Step 5. Once you have copied and Pasted the above Code in a Module, you can use the Below Formula in your Workbook in any WorkSheet as shown in Below Picture:
FREE DOWNLOAD - Numbers to Words Convertor
Finally, you can download your FREE Excel workbook with UDF to convert numbers to word. Moreover, You can also look into the code and customize it to fit your need.
Of course, you can share your customized version here for other visitors who may get benefited – after all sharing is caring !!
Currently, this excel file has two user-defined functions – One for the Indian Number Scale System and the other for the American English Scale system.
To use the UDF in Excel 365 (aka 2019) the file should as .xlam