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:

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.