# How to Convert Numbers into Words in Excel Using VBA?

Jul 9, 2023

## Introduction:

How to Convert Numbers into Words in Excel Using VBA?

Excel doesn’t have a default function that displays numbers as English words in a worksheet, but you can add this capability by pasting the following SpellNumber function code into a VBA (Visual Basic for Applications) module.

Excel is a powerful tool for data analysis and manipulation, but it’s not always straightforward when it comes to converting numbers into words. However, with a little bit of VBA (Visual Basic for Applications) programming, you can easily convert numerical values into their corresponding words. In this article, we will explore a VBA code snippet that accomplishes this task and provides a step-by-step explanation of the code.

## Step 1: Open the Visual Basic Editor

To get started, open the Visual Basic Editor in Excel. You can do this by pressing “Alt + F11” or navigating to the “Developer” tab and clicking on the “Visual Basic” button.

## Step 2: Insert a New Module

In the Visual Basic Editor, click on “Insert” in the top menu and select “Module.” This will create a new module where we’ll write our VBA code.

## Step 3: Write the VBA Code

Copy and paste the following VBA code into the module:

``````Function SpellNumber(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

'SpellNumber = "Rupees "

ElseIf Val(Left(FIGURE, 9)) = 1 Then

'SpellNumber = "Rupee "

End If

For i = 1 To 3

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

If i = 1 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " Crore "

ElseIf i = 2 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " Lakh "

ElseIf i = 3 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & " Thousand "

End If

FIGURE = Mid(FIGURE, 3)

Next i

If Val(Left(FIGURE, 1)) > 0 Then

SpellNumber = SpellNumber & 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

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

FIGURE = Mid(FIGURE, 4)

If Val(FIGURE) > 0 Then

SpellNumber = SpellNumber & " dot "

If Val(Left(FIGURE, 2)) < 20 And Val(Left(FIGURE, 2)) > 0 Then

SpellNumber = SpellNumber & WORDs(Val(Left(FIGURE, 2)))

ElseIf Val(Left(FIGURE, 2)) > 19 Then

SpellNumber = SpellNumber & tens(Val(Left(FIGURE, 1)))

SpellNumber = SpellNumber & WORDs(Val(Right(Left(FIGURE, 2), 1)))

End If

End If

FIGURE = amt

FIGURE = Format(FIGURE, "FIXED")

If Val(FIGURE) > 0 Then

SpellNumber = SpellNumber & " "

End If

End Function

``````

## Step 4: Save and Close the Visual Basic Editor

Save the workbook to retain the VBA code, and then close the Visual Basic Editor.

## Step 5: Use the Custom Function

Now that we have defined the VBA code, you can use the custom function `=SpellNumber` in your Excel worksheet. To do this, follow these steps:

1. In a cell, enter a numerical value that you want to convert to words.
2. In another cell, enter the following formula:
• `=SpellNumber`(A2)
3. Press Enter to see the converted number in words.

## You May Also Like

#### By seoanalyser

SEOAnalyser is a powerful tool for analyzing and improving your website's SEO performance. Our website provides a comprehensive suite of tools to help you optimize your website, including keyword analysis, competitor analysis, website analysis, and more. Try SEOAnalyser today and take your website's SEO to the next level!