Excel VBA Convert SpellNumber – Amount (Numbers) To Words – Both US & India Formats

Amount in Numbers To Words – Both Million & Crores

Get Amount converted from Number to Words both in Millions & Crores format.

In many accounting, billing & invoicing Excel app, we need to convert the Amounts in Numbers To Words.

It was older convention to write down using pen in numbers & words to double confirm that no one changes the numbers after a transaction is confirmed.

So, for the same convention, the modern automated billing, invoice apps also follow this method.

SpellNumbers – Amount to Words – Rupee & Dollar

It can create words for number formats in thousand, Million, Billion & up to trillion. In case if you are looking for Indian Number formatting in Lakhs, Crores – it is still under development.

Option Explicit
'SpellNumber
'Function Converts Number to Words
'0 for US format, 1 for Indian Format
Function Num2Words(iAmtNumber As Double, iCurrencyFormat As Integer) As String
    Dim sAmt As String, sDecimal As String, sText As String
    Dim sResultWords As String, sResultWordsDecimal As String
    Dim CurrencyUnit As String, CurrencylUnitDecimal As String
    Dim iDecimalIdx, iPlaceIdx As Double
    Dim bFirstEntry As Boolean, dCurrencyFormatSplit As Double
    
    CurrencyUnit = "Dollars"
    CurrencylUnitDecimal = "Cents"
    
    'US Number Format String - Dollars & Cents
    Dim sPlace0(9) As String
    sPlace0(2) = " Thousand "
    sPlace0(3) = " Million "
    sPlace0(4) = " Billion "
    sPlace0(5) = " Trillion "
    
    'Indian Number Format String - Rupees & Paise
    Dim sPlace1(9) As String
    sPlace1(2) = " Thousand "
    sPlace1(3) = " Lakhs "
    sPlace1(4) = " Crores "
    
    'Pre Process Amount Numbers
    sAmt = VBA.Trim(VBA.Str(iAmtNumber))
    sAmt = VBA.Replace(sAmt, ",", "", , , vbTextCompare)
    sAmt = VBA.Replace(sAmt, " ", "", , , vbTextCompare)
    
    'Split Decimal & Integer Part
    iDecimalIdx = VBA.InStr(sAmt, ".")
    If iDecimalIdx > 0 Then
        sDecimal = VBA.Mid(sAmt, iDecimalIdx + 1, 2)
        sAmt = VBA.Mid(sAmt, 1, iDecimalIdx - 1)
        
        'Get Decimals to Words
        sResultWordsDecimal = GetTens(sDecimal)
        sResultWordsDecimal = sResultWordsDecimal & " " & CurrencylUnitDecimal
    End If
    
    'Process Amount Each Thousand
    sText = ""
    iPlaceIdx = 1
    
    'First Thousand in Amount
    If sAmt <> "" Then
        sText = GetHundreds(VBA.Right(sAmt, 3))
        If sText <> "" Then sResultWords = sText
        If VBA.Len(sAmt) > 3 Then
            sAmt = VBA.Left(sAmt, VBA.Len(sAmt) - 3)
        Else
            sAmt = ""
        End If
        iPlaceIdx = iPlaceIdx + 1
    End If
    
    'Remaining Digits in Amount
    Do While sAmt <> ""
        If iCurrencyFormat > 0 Then
            sText = GetTens(VBA.Right(sAmt, 2))
            If sText <> "" Then sResultWords = sText & sPlace1(iPlaceIdx) & sResultWords
            If VBA.Len(sAmt) > 2 Then
                sAmt = VBA.Left(sAmt, VBA.Len(sAmt) - 2)
            Else
                sAmt = ""
            End If
        Else
            sText = GetHundreds(VBA.Right(sAmt, 3))
            If sText <> "" Then sResultWords = sText & sPlace0(iPlaceIdx) & sResultWords
            If VBA.Len(sAmt) > 3 Then
                sAmt = VBA.Left(sAmt, VBA.Len(sAmt) - 3)
            Else
                sAmt = ""
            End If
        End If
        iPlaceIdx = iPlaceIdx + 1
    Loop
    sResultWords = sResultWords & " " & CurrencyUnit
    
    If sResultWordsDecimal <> "" Then
        sResultWords = sResultWords & " and " & sResultWordsDecimal
    End If
    
    Num2Words = sResultWords & " Only"
End Function
      
' Converts a number from 100-999 into text
Function GetHundreds(ByVal numAmt)
    Dim Result As String
    If Val(numAmt) = 0 Then Exit Function
    numAmt = Right("000" & numAmt, 3)
    ' Convert the hundreds place.
    If Mid(numAmt, 1, 1) <> "0" Then
        Result = GetDigit(Mid(numAmt, 1, 1)) & " Hundred "
    End If
    ' Convert the tens and ones place.
    If Mid(numAmt, 2, 1) <> "0" Then
        Result = Result & GetTens(Mid(numAmt, 2))
    Else
        Result = Result & GetDigit(Mid(numAmt, 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 VBA.Len(TensText) < 2 Then
        Result = GetDigit(TensText)
        GetTens = Result
        Exit Function
    End If
    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

This code can be used a User defined function. i.e., the function can be used directly in the Excel worksheet.

Example: =num2words(123) will give output as “One Hundred Twenty Three Dollars and No Cents”.

If the currency format is different from Dollars, change the corresponding field in the code. This code will display the amount in the specific currency format as per users input.

External Reference (SpellNumber Function) :

The three important supporting functions GetHundreds, GetTens, GetDigit are copied from this page. The main function is altered to incorporate different currency formats.