How to convert a numeric value into English words in Excel
Summary
This
article contains sample Microsoft Visual Basic for Applications functions that
you can use to convert a numeric value in a Microsoft Excel worksheet cell into
its equivalent in English words.
More
Information
This
article assumes that you are familiar with the programming language that is
being demonstrated and with the tools that are used to create and to debug
procedures.
How to create the sample function Called Spellwords
1. Start Microsoft Excel.
2. Press ALT+F11 to start the
Visual Basic Editor.
3. On the Insert menu, click Module.
4. Type the following code
into the module sheet.
Function Spellwords(amt As Variant) As Variant
Dim FIGURE As Variant
Dim LENFIG As Integer
Dim i As Integer
Dim WORDs(19) As String
Dim tens(9) As String
WORDs(1) = "One"
WORDs(2) = "Two"
WORDs(3) = "Three"
WORDs(4) = "Four"
WORDs(5) = "Five"
WORDs(6) = "Six"
WORDs(7) = "Seven"
WORDs(8) = "Eight"
WORDs(9) = "Nine"
WORDs(10) = "Ten"
WORDs(11) = "Eleven"
WORDs(12) = "Twelve"
WORDs(13) = "Thirteen"
WORDs(14) = "Fourteen"
WORDs(15) = "Fifteen"
WORDs(16) = "Sixteen"
WORDs(17) = "Seventeen"
WORDs(18) = "Eighteen"
WORDs(19) = "Nineteen"
tens(2) = "Twenty"
tens(3) = "Thirty"
tens(4) = "Fourty"
tens(5) = "Fifty"
tens(6) = "Sixty"
tens(7) = "Seventy"
tens(8) = "Eighty"
tens(9) = "Ninety"
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
FIGLEN = Len(FIGURE)
If FIGLEN < 12 Then
FIGURE = Space(12 - FIGLEN) & FIGURE
End If
If Val(Left(FIGURE, 9)) > 1 Then
Spellwords = "Rupees ("
ElseIf Val(Left(FIGURE, 9)) = 1 Then
Spellwords = "Rupee "
End If
For i = 1 To 3
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Spellwords = Spellwords & tens(Val(Left(FIGURE, 1)))
Spellwords = Spellwords & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
If i = 1 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & " Crore "
ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & " Lakh "
ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & " Thousand "
End If
FIGURE = Mid(FIGURE, 3)
Next i
If Val(Left(FIGURE, 1)) > 0 Then
Spellwords = Spellwords & WORDs(Val(Left(FIGURE, 1))) + " Hundred "
End If
FIGURE = Mid(FIGURE, 2)
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Spellwords = Spellwords & tens(Val(Left(FIGURE, 1)))
Spellwords = Spellwords & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
FIGURE = Mid(FIGURE, 4)
If Val(FIGURE) > 0 Then
Spellwords = Spellwords & " and Paise "
If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then
Spellwords = Spellwords & WORDs(Val(Left(FIGURE, 2)))
ElseIf Val(Left(FIGURE, 2)) > 19 Then
Spellwords = Spellwords & tens(Val(Left(FIGURE, 1)))
Spellwords = Spellwords & WORDs(Val(Right(Left(FIGURE, 2), 1)))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE, "FIXED")
If Val(FIGURE) > 0 Then
Spellwords = Spellwords & ") Only "
End If
End Function
How to use the Spellwords
sample function
To use the sample functions to change a number to written
text, use one of the methods demonstrated in the following examples:
Method 1: Direct Entry
You can change 67.35 into "
Rupees (SixtySeven and Paise ThirtyFive) Only" by entering the following formula into a cell:
=Spellwords(67.35)
Method 2: Cell reference
You can refer to other cells in the workbook. For
example, enter the number 67.35 into cell A1, and type the following formula
into another cell:
=Spellwords(A1)
Method 3: Insert Function
To use Insert Function, follow
these steps:
Excel 2003:
Excel 2003:
1.
Select
the cell that you want.
2. Click Insert
Function on the Standard toolbar.
3. Under Or select a
category, click User Defined.
4. In the Select a
function list, click Spellwords, and then click OK.
5. Enter the number or cell
reference that you want, and then click OK.
Excel 2007 and 2010:
1.
Select the cell that you want.
2.
Click Insert Function on
the Formulas ribbon.
3.
Under Or select a category,
click User Defined.
4.
In the Select a function list,
click Spellwords, and then click OK.
Enter the number or cell
reference that you want, and then click OK.
How to change the sample
function Called Spellwords to other name
1. Start Microsoft Excel.
2. Press ALT+F11 to start the
Visual Basic Editor.
3. On the Insert menu,
click Module.
4. In the module sheet press Ctrl+H
then in Find What place insert Spellwords and in Replace with insert your
desire name then click on Replace all.
