How To Convert Amount To Words In Excel

Hello there!

In this tutorial, I am going to show you a Visual Basic Script for converting amount to words in excel.

Prerequisites:

    - The reader is expected to know how to use Excel VBA (Visual Basic for Applications) in order to follow the following instructions.

1. First, open your excel application

2. Navigate to the VBA by pressing Alt+F11
(This is pressing they 'Alt' key together with 'F11' key).

3. From there, create a new module on the left pane by right-clicking on the VBA Project empty area, select Insert>Module.

4. Paste the below code on the code window.

	Option Explicit
	Function AmountToWords(ByVal Amount)
	Dim Pesos, Cents, Temp
	Dim DecimalPlace, Count
	ReDim Place(9) As String
	Place(2) = " Thousand "
	Place(3) = " Million "
	Place(4) = " Billion "
	Place(5) = " Trillion "
	' String representation of amount.
	Amount = Trim(Str(Amount))
	' Position of decimal place 0 if none.
	DecimalPlace = InStr(Amount, ".")
	' Convert cents and set Amount to Peso amount.
	If DecimalPlace > 0 Then
	Cents = GetTens(Left(Mid(Amount, DecimalPlace + 1) & _
	"00", 2))
	Amount = Trim(Left(Amount, DecimalPlace - 1))
	End If
	Count = 1
	Do While Amount <> ""
	Temp = GetHundreds(Right(Amount, 3))
	If Temp <> "" Then Pesos = Temp & Place(Count) & Pesos
	If Len(Amount) > 3 Then
	Amount = Left(Amount, Len(Amount) - 3)
	Else
	Amount = ""
	End If
	Count = Count + 1
	Loop
	Select Case Pesos
	Case ""
	Pesos = ""
	Case "One"
	Pesos = "One Peso"
	Case Else
	Pesos = Pesos & " Pesos"
	End Select
	Select Case Cents
	Case ""
	Cents = ""
	Case "One"
	Cents = " and One Centavo"
	Case Else
	Cents = " and " & Cents & " Centavos"
	End Select
	AmountToWords = Pesos & Cents
	End Function
	' Converts a number from 100-999 into text
	Function GetHundreds(ByVal Amount)
	Dim ans As String
	If Val(Amount) = 0 Then Exit Function
	Amount = Right("000" & Amount, 3)
	' Convert the hundreds place.
	If Mid(Amount, 1, 1) <> "0" Then
	ans = GetOnesDigit(Mid(Amount, 1, 1)) & " Hundred "
	End If
	' Convert the tens and ones place.
	If Mid(Amount, 2, 1) <> "0" Then
	ans = ans & GetTens(Mid(Amount, 2))
	Else
	ans = ans & GetOnesDigit(Mid(Amount, 3))
	End If
	GetHundreds = ans
	End Function
	' Converts a number from 10 to 99 into text.
	Function GetTens(TensText)
	Dim ans As String
	ans = ""
	If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
	Select Case Val(TensText)
	Case 10:
	ans = "Ten"
	Case 11:
	ans = "Eleven"
	Case 12:
	ans = "Twelve"
	Case 13:
	ans = "Thirteen"
	Case 14:
	ans = "Fourteen"
	Case 15:
	ans = "Fifteen"
	Case 16:
	ans = "Sixteen"
	Case 17:
	ans = "Seventeen"
	Case 18:
	ans = "Eighteen"
	Case 19:
	ans = "Nineteen"
	Case Else
	End Select
	Else ' If value between 20-99...
	Select Case Val(Left(TensText, 1))
	Case 2: ans = "Twenty "
	Case 3: ans = "Thirty "
	Case 4: ans = "Forty "
	Case 5: ans = "Fifty "
	Case 6: ans = "Sixty "
	Case 7: ans = "Seventy "
	Case 8: ans = "Eighty "
	Case 9: ans = "Ninety "
	Case Else
	End Select
	ans = ans & GetOnesDigit _
	(Right(TensText, 1)) ' Get ones place.
	End If
	GetTens = ans
	End Function
	' Numbers from 1 to 9 into text.
	Function GetOnesDigit(Digit)
	Select Case Val(Digit)
	Case 1:
	GetOnesDigit = "One"
	Case 2:
	GetOnesDigit = "Two"
	Case 3:
	GetOnesDigit = "Three"
	Case 4:
	GetOnesDigit = "Four"
	Case 5:
	GetOnesDigit = "Five"
	Case 6:
	GetOnesDigit = "Six"
	Case 7:
	GetOnesDigit = "Seven"
	Case 8:
	GetOnesDigit = "Eight"
	Case 9:
	GetOnesDigit = "Nine"
	Case Else:
	GetOnesDigit = ""
	End Select
	End Function

view raw gistfile1.txt hosted with ❤ by GitHub

5. Save the file as '.xls' or '.xlsm'. The first file format is for Excel 97-2003 and the latter if for the newer version of excel which means Macro-Enabled.

     Note: You may notice that the currency I used is 'Peso', just replace it in the code for your preferences..

6. After that, we can now start using our function.

7. To use the function, select the cell where you want the converted amount to be displayed and input the following formula:

    =AmountToWords(number)

The 'number' attribute is the number you want to convert to words.

I hope this helps as it does on my colleagues. Thanks and enjoy...