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/nu...rds-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

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,10},{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","Fif ty","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),L OOKUP(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),LOOK UP(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(T EXT(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","T hirty","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",LOOKU P(MID(TEXT(A1,0),3,1),{"0","1","2","3","4","5","6" ,"7","8","9"},{"","","","","","","","","",""}),LOO KUP(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"},{"","On e 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"}),LOOKU P(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","T hirty","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),LOO KUP(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(M ID(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(M ID(A1,FIND(".",A1)+2,1),{"0","1","2","3","4","5"," 6","7","8","9"},{"","","","","","","","","",""}),L OOKUP(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