Results 1 to 9 of 9

Thread: Number to Words (Rupees)

  1. #1
    Junior Member
    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0

    Number to Words (Rupees)

    The VBA coding UDF word is like this as I have read
    Code:
    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.
    Last edited by Admin; 06-20-2014 at 01:23 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  4. #4
    Junior Member
    Join Date
    Apr 2012
    Posts
    3
    Rep Power
    0

    Modifying number to word only (Not Rupees)

    The VBA coding UDF word is like this as I have read
    Code:
    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

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    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.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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."))
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    (Mr. Fox)
    Is it possible to convert this format to Office 2003 ?
    Last edited by mahmoud-lee; 02-24-2014 at 08:28 PM.

  8. #8
    Member
    Join Date
    Jul 2012
    Posts
    55
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    (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.

  9. #9
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    Thank you for your answer

Similar Threads

  1. Yet Another Number-To-Words Function (Sorry, US Style Only)
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 10
    Last Post: 08-06-2020, 02:44 PM
  2. Number into words (Rupees)
    By Admin in forum Download Center
    Replies: 2
    Last Post: 06-14-2014, 12:05 AM
  3. Change Number Written In Words To Numeric Values
    By Safal Shrestha in forum Excel Help
    Replies: 1
    Last Post: 04-03-2013, 03:01 PM
  4. Modifying number to word only (Not Rupees)
    By Excel Fox in forum Excel Help
    Replies: 0
    Last Post: 04-01-2012, 03:46 PM
  5. Number to Words (Rupees)
    By sa.1985 in forum Excel Help
    Replies: 2
    Last Post: 12-16-2011, 08:57 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •