First of all, wouldn’t it be great if we have a built in function in Microsoft Excel to Convert **Numbers to Letters. ** It means, simply, by using a formula in excel, I could Spell Numbers in Words.

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 on invoices or on Bank Cheques.

**For Example: Number : 6734862**

We can write above number in words in many ways in different languages, countries and Number scales etc. Here are the two, for which, I have adjusted the function.

**In American Engligh :**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.

## Limitation 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 that and returns 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 using**recursive function **. In simple language –* if a function call itself from its own body of 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 more modular way which will be 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**. Benefit of creating this as a separate function is that you can use this function to convert any types 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 in words, you can split whole and decimal number and call this function separately. This is what I have done in *SpellCurrency* functions below. You can refer 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 above code is for 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 Whole number and one for Decimal part(rounded to two digit)**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:

# UDF to convert Number to Words – FREE Download

Finally, you can download your FREE Excel workbook with UDF to convert numbers to word. Moreover, You can also look in to the code and customize it to fit your need.

Ofcourse, 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 function – One for Indian Number Scale System and other for American English Scale system.

Finally here you go with your FREE Download file to play around !!

NPJune 27, 2019 at 1:11 amTo use the UDF in Excel 365 (aka 2019) the file should as .xlam