Convert numbers into English words in MS Excel
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.
