Number to Words (Rupees)

[vishwajeet_chakravorty]

The VBA coding UDF word is like this as I have read

Function words(fig, Optional point = "Point") As String
Dim digit(14) As Integer
alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i - 1) + 10: digit(i - 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, "thousand ", "million ", "billion ") & words
words = Trim(alpha(digit(i)) & " " & words)
Next
If fig <> Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ""
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))
words = Trim(words & " " & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & " " & alpha(Mid(figd, i, 1))
Else: words = words & " Zero"
End If
Next
End If
If fig < 0 Then words = "Negative " & words
End Function

In India the units are like this
1 Unit
10 Ten
100 1 hundred
1,000 1 thousand
10,000 10 thousand
1,00,000 1 lakh
10,00,000 10 lakh
1,00,00,000 1 crore
10,00,00,000 10 crore
1,00,00,00,000 1 arab
10,00,00,00,000 10 arab
1,00,00,00,00,000 1 kharab
10,00,00,00,00,000 10 kharab
1,00.00.00.00.00.000 1 neel
10,00,00,00,00,00,000 10 neel
1,00,00,00,00,00,00,000 1 padm
10,00,00,00,00,00,00,000 10 padm
1,00,00,00,00,00,00,00,000 1 shankh
10,00,00,00,00,00,00,00,000 10 shankh
1,00,00,00,00,00,00,00,00,000 1 samudra
10,00,00,00,00,00,00,00,00,000 10 samudra
1,00,00,00,00,00,00,00,00,00,000 1 antya
10,00,00,00,00,00,00,00,00,00,000 10 antya

How the codes can be modified. Kindly help me.

[Admin]

Hi Vishwajeet,

Welcome to board !!!

Please use code tags while posting code. I have added the tags on your other thread. Please edit your thread and use the tags.

[Admin]

Have a look at http://www.excelfox.com/forum/f12/number-into-words-rupees-45/

[vishwajeet_chakravorty]

The VBA coding UDF word is like this as I have read

Function words(fig, Optional point = "Point") As String
Dim digit(14) As Integer
alpha = Array("", "One", "Two", "Three", "Four", "Five", "Six", "Seven", "Eight", "Nine", "Ten", "Eleven", "Twelve", "Thirteen", "Fourteen", "Fifteen", "Sixteen", "Seventeen", "Eighteen", "Nineteen", "Twenty", "Thirty", "Forty", "Fifty", "Sixty", "Seventy", "Eighty", "Ninety")
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i - 1) + 10: digit(i - 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = "hundred " & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, "thousand ", "million ", "billion ") & words
words = Trim(alpha(digit(i)) & " " & words)
Next
If fig <> Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ""
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & ".", ""))
words = Trim(words & " " & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & " " & alpha(Mid(figd, i, 1))
Else: words = words & " Zero"
End If
Next
End If
If fig < 0 Then words = "Negative " & words
End Function

In India the units are like this
1 Unit
10 Ten
100 1 hundred
1,000 1 thousand
10,000 10 thousand
1,00,000 1 lakh
10,00,000 10 lakh
1,00,00,000 1 crore
10,00,00,000 10 crore
1,00,00,00,000 1 arab
10,00,00,00,000 10 arab
1,00,00,00,00,000 1 kharab
10,00,00,00,00,000 10 kharab
1,00.00.00.00.00.000 1 neel
10,00,00,00,00,00,000 10 neel
1,00,00,00,00,00,00,000 1 padm
10,00,00,00,00,00,00,000 10 padm
1,00,00,00,00,00,00,00,000 1 shankh
10,00,00,00,00,00,00,00,000 10 shankh
1,00,00,00,00,00,00,00,00,000 1 samudra
10,00,00,00,00,00,00,00,00,000 10 samudra
1,00,00,00,00,00,00,00,00,00,000 1 antya
10,00,00,00,00,00,00,00,00,00,000 10 antya

How the codes can be modified. Kindly help me

[Admin]

Hi

You are violating the forum rules again. Please acknowledge what I told in my earlier post first. You stick with the original thread and do not start duplicate thread.

[Excel Fox]

Here's an Excel formula from Indian Currency Number to Word (http://www.ozgrid.com/forum/showthread.php?t=176326)

Works really fast for such a long formula

=TRIM(CONCATENATE(IFERROR(LOOKUP(MID(TEXT(A1,0),LO OKUP(LEN(TEXT(A1,0)),{10},{1}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {9,10},{1,2}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{9,1 0},{2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Crore","Twelve Crore","Thirteen Crore","Fourteen Crore","Fifteen Crore","Sixteen Crore","Seventeen Crore","Eighteen Crore","Nineteen Crore"}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{9 ,10},{1,2}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {8,9,10},{11,1,2}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{8,9 ,10,11},{11,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{7 ,8,9,10},{12,1,2,3}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Crore","One Crore","Two Crore","Three Crore","Four Crore","Five Crore","Six Crore","Seven Crore","Eight Crore","Nine Crore"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {7,8,9,10},{1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{7,8 ,9,10},{2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Lac","Twelve Lac","Thirteen Lac","Fourteen Lac","Fifteen Lac","Sixteen Lac","Seventeen Lac","Eighteen Lac","Nineteen Lac"}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{7 ,8,9,10},{1,2,3,4}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {6,7,8,9,10},{11,1,2,3,4}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{6,7 ,8,9,10},{11,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{5 ,6,7,8,9,10},{11,1,2,3,4,5}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Lac","One Lac","Two Lac","Three Lac","Four Lac","Five Lac","Six Lac","Seven Lac","Eight Lac","Nine Lac"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {5,6,7,8,9,10},{1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4,5 ,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten Thousand","Eleven Thousand","Twelve Thousand","Thirteen Thousand","Forteen Thousand","Fifteen Thousand","Sixteen Thousand","Seventeen Thousand","Eighteen Thousand","Nineteen Thousand"}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{5 ,6,7,8,9,10},{1,2,3,4,5,6}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {4,5,6,7,8,9,10},{12,1,2,3,4,5,6}),1)="0",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4,5 ,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}),IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4,5,6 ,7,8,9,10},{12,1,2,3,4,5,6}),1)="1",LOOKUP(MID(TEXT(A1,0),3,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{4 ,5,6,7,8,9,10},{1,2,3,4,5,6,7}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Thousand","One Thousand","Two Thousand","Three Thousand","Four Thousand","Five Thousand","Six Thousand","Seven Thousand","Eight Thousand","Nine Thousand"}))),"")," ",IFERROR(LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1, 0)),{3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Hundred","Two Hundred","Three Hundred","Four Hundred","Five Hundred","Six Hundred","Seven Hundred","Eight Hundred","Nine Hundred"}),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{1,2 ,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven","Twelve","Thirteen","Fourteen","Fifteen","Sixteen","Seventeen","Eighteen","Nineteen"}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{1 ,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)), {1,2,3,4,5,6,7,8,9,10},{11,1,2,3,4,5,6,7,8,9}),1)="1",LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{1,2 ,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(TEXT(A1,0),LOOKUP(LEN(TEXT(A1,0)),{1 ,2,3,4,5,6,7,8,9,10},{1,2,3,4,5,6,7,8,9,10}),1),{"0","1","2","3","4","5","6","7","8","9"},{"","One","Two","Three","Four","Five","Six","Seven","Eight","Nine"})),"")," ","Rupee"," ",IFERROR(IF(LEN(FIND(".",A1))>0,"And",""),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,2)="1","Ten Paise",""),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,1)="1",LOOKUP(MID(A1,FIND(".",A1)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"Ten","Eleven Paise","Twelve Paise","Thirteen Paise","Fourteen Paise","Fifteen Paise","Sixteen Paise","Seventeen Paise","Eighteen Paise","Nineteen Paise"}),LOOKUP(MID(A1,FIND(".",A1)+1,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","Twenty","Thirty","Forty","Fifty","Sixty","Seventy","Eighty","Ninety"})),"")," ",IFERROR(IF(MID(A1,FIND(".",A1)+1,1)="1",LOOKUP(MID(A1,FIND(".",A1)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","","","","","","","","",""}),LOOKUP(MID(A1,FIND(".",A1)+2,1),{"0","1","2","3","4","5","6","7","8","9"},{"","One Paisa","Two Paise","Three Paise","Four Paise","Five Paise","Six Paise","Seven Paise","Eight Paise","Nine Paise"})),"")," ","Only."))

[mahmoud-lee]

(Mr. Fox)
Is it possible to convert this format to Office 2003 ?

[Ingolf]

(Mr. Fox)
Is it possible to convert this format to Office 2003 ?

No, you can not. Even if it can replace some functions (IFERROR does not exist in Excel 2003) would be too many IF's.

[mahmoud-lee]

Thank you for your answer