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

## Spell Numbers – Amount to Words – Rupee & Dollar

This code is just a modified version from this link.

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 '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:**

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