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.
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.

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 usingrecursive 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

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

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 !!

DOWNLOAD – RegEx Tester – Excel VBA

cover3d_0-89071700_1484285537__1_

Join over 10, 000+ Excel VBA Enthusiasts & get this FREE e-Book Now!