UDF to Convert Numbers to Letters

.

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

For Example: Number : 6734862
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.
Numbers to Letters

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

Basically, this function does the following:

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:
Numbers to Letters

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.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. NP

    To use the UDF in Excel 365 (aka 2019) the file should as .xlam

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest