Convert numbers into English words in MS Excel

Asked By 20 points N/A Posted on -
qa-featured

Hi,

I need to convert numeric values to English words in MS Excel.

I have researched for answers from Google but as of now, I haven't found the right formula to use.

I was able to get a hold of this Visual Basic function script from this site, but I think there might be a mistake in the function.

I cannot apply it as it yields the result #NAME?. 

Can anybody check for the correctness of the function (shown below)? 

Also I need the result to be in the currency of a SAR or Saudi Arabian Riyals having a provision for decimal equivalents:

End Result should be like this: SAR One hundred seventy five & 35/100 only.

Is it possible to attain the result that I wanted?

Function Ntow (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) = "Forty"
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
Ntow= "Rupees "
Elseif Val(Left(figure,9)) = 1 Then
Ntow = "Rupee "
End If

For i = 1 To 3
If Val(Left(figure,2)) < 20 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If

If i = 1 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Crore "
Elseif i = 2 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Lakh "
Elseif i = 3 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & " Thousand "
End If
figure = Mid(figure,3)
Next i

If Val(Left(figure,1)) > 0 Then
Ntow = Ntow & 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
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If
figure = Mid(figure,4)

If Val(figure) > 0 Then
Ntow = Ntow & " Paise "
If Val(Left(figure,2)) < 20 And Val(Left(figure,2)) > 0 Then
Ntow = Ntow & words(Val(Left(figure,2)))
Elseif Val(Left(figure,2)) > 19 Then
Ntow = Ntow & tens(Val(Left(figure,1)))
Ntow = Ntow & words(Val( Right(Left(figure,2),1) ))
End If
End If
FIGURE = amt
FIGURE = Format(FIGURE,"FIXED")
If Val(figure) > 0 Then
Ntow = Ntow & " Only "
End If
End Function

THANKS.

SHARE
Best Answer by Gill Bros
Best Answer
Best Answer
Answered By 200 points N/A #130874

Convert numbers into English words in MS Excel

qa-featured

VBA Code to Convert Numbers into English Words.

Is it possible to attain the result that I wanted?

Yes! The VBA code you have given is working. You only have to modify some parts of the code for the result you want to have.

You obtain the result #NAME because you have not used the VBA function correctly. You must put a valid parameter or a reference to the function. The function was declared as Function NTOW (Amt As Variant) As Variant.

The function NTOW expecting the Amt parameter where inside the function was processed to convert into word format. Here’s sample image to use the function:

Microsoft excel

The image above shows the syntax of the function in Column C. This also shows the output of the original VBA code.

Below is the result of the edited VBA code based on your desired output.

edited VBA code based on your desired output

Take note of the formula on column B (noted on column C), on B1 the NTOW function refers to the value of A1 while on B3, it refers to the value directly passed to NTOW function which is the 11.5.

In this figure I have already replaced the “Rupee” with “SAR” as your required output want to see.

Where did I change the code? Navigate the code down to row 45 and will found the statement NTOW is equal to “Rupees “. Just change the word Rupees inside the double quotes wit SAR. Also change the word “Rupee” on line 47 with SAR.

Here’s the code for your reference:

he word Rupees inside the double quotes wit SAR

As you will see on the sample output of the function posted on the image above it says:

Rupees Eleven Paise Fifty Only.

The word “Paise” must be replaced with “&” as what you have put in your requirements for the output. The VBA statement was found in the 83rd row in the VBA code. Here it is:

notepad

Replace the statement with this statement to change the output word “Paise” with “&”:

Ntow = Ntow & “ & “

Now, on the decimal section which you want to see is the fraction not the word. Example a 0.10 value will be 10/100 and 0.99 is equal to 99/100. This will be our next job on the VBA code.

Ntow = Ntow & “ & “

Starting on Line 84 up to Line 89 of the VBA Code, the statement converts also the decimal number into English words. In order to avoid conversion of the decimal values and instead convert it into fractions.

Since we need to test only the decimal part of the value if it is greater than zero or not. So that we can decide the need to display the fraction form of the decimal part and leave it empty if zero or none.

So our condition must be if Val(Left(FIGURE, 2)) > 0 our action is to display the decimal part into fractions. So we need to implement this VBA code if Val(Left(FIGURE, 2)) > 0 then Now = Now & Val(Left(FIGURE, 2)) & "/100".

Take note of the Val(Left(FIGURE, 2)) just gets the decimal part then concatenate with the string value “/100”. Here’s the full snippet of the VBA Code for the modification on line 84 up to line 89.

Ntow = Ntow & Val(Left(FIGURE, 2)) & "/100".

Attached here is the complete modification of the function. Please download attachment and paste it into your VBA code.

Answered By 0 points N/A #130875

Convert numbers into English words in MS Excel

qa-featured

The solution to This is working fine, but it's in Thousands and Lakhs, Anybody please help me to get a code for converting the same to hundreds and Millions?

Login/Register to Answer

Related Questions