Thursday, July 2, 2009

Excel Functions : Convert Numbers into Words

Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. However there are customised functions available on the Internet. One such solution is available at Allexperts.com.

Display Numbers to Text.

You need to copy this to your regular macro module. Once you have added it to your file you can use function SpellNumbers to convert any number into words easily as you use any other function of excel.

Function available at the net covers USD as currency, whereas I needed it in Indian Rupees. I have modified this to give results in any currency. The revised version gives me results as shown in the screen cast below.

Photobucket


Download excel file having this user defined function to convert numbers to words


Make sure that you enable macros to use this function. In case macros are disabled this function will not work in downloaded file

Syntex for the modified UDF is :-
SpellCurr(MyNumber, MyCurrency, MyCurrencyPlace, MyCurrencyDecimals, MyCurrencyDecimalsPlace)

where
MyNumber = Numeric Value you need to convert into words
MyCurrency = Name of your Currency - i.e. Dollar for USA
MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix
MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA
MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix

Modified code given below for those who want to use it. Currency inputs are optional and you will not need to input currency details in case you are using it for Indian Currency. Still this can be used for any currency provided you give currency inputs.

You will need to copy this code to regular VBA module of your workbook

Function SpellCurr(ByVal MyNumber, _
Optional MyCurrency As String = "Rupee", _
Optional MyCurrencyPlace As String = "P", _
Optional MyCurrencyDecimals As String = "Paisa", _
Optional MyCurrencyDecimalsPlace As String = "S")

'*****************************************************************************************************************
'* Based on SpellNumbers UDF by Microsoft, Which handles only Dollars as currency *
'* UDF modfied by Yogesh Gupta, smiley123z@gmail.com, Ygblogs.blogspot.com on July 21, 2009 *
'* UDF modified on September 04, 2009 to make currency inputs optional, by default it will use Indian Currency *
'* This modified UDF can be used for any currency in case you provide for currency inputs *
'* User can define the Prefix and Sufix place for Currency and CurrencyDecimals *
'* MyNumber = Numeric Value you need to convert into words *
'* MyCurrency = Name of your Currency - i.e. Dollar for USA *
'* MyCurrencyPlace = Prefix or Suffix the currency, use "P" for Prefix and "S" for Suffix *
'* MyCurrencyDecimals = Name of your Currency Decimals - i.e. Cent for USA *
'* MyCurrencyDecimalsPlace = Prefix or Suffix the currency decimals, use "P" for Prefix and "S" for Suffix *
'*****************************************************************************************************************

Dim Rupees, Paisa, Temp
Dim DecimalPlace, Count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

'String representation of amount.
MyNumber = Trim(Str(MyNumber))

'Position of decimal place 0 if none.
DecimalPlace = InStr(MyNumber, ".")

' Convert Paisa and set MyNumber to Rupee amount.
If DecimalPlace > 0 Then
Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

Count = 1

Do While MyNumber <> ""
Temp = GetHundreds(Right(MyNumber, 3))
If Temp <> "" Then Rupees = Temp & Place(Count) & Rupees
If Len(MyNumber) > 3 Then
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
Count = Count + 1

Loop

If MyCurrencyPlace = "P" Then
Select Case Rupees
Case ""
Rupees = MyCurrency & "s" & " Zero"
Case "One"
Rupees = MyCurrency & " One"
Case Else
Rupees = MyCurrency & "s " & Rupees
End Select
Else
Select Case Rupees
Case ""
Rupees = "Zero " & MyCurrency & "s"
Case "One"
Rupees = "One " & MyCurrency
Case Else
Rupees = Rupees & " " & MyCurrency & "s"
End Select
End If

If MyCurrencyDecimalsPlace = "S" Then
Select Case Paisa
Case ""
Paisa = " Only"
Case "One"
Paisa = " and One " & MyCurrencyDecimals & " Only"
Case Else
Paisa = " and " & Paisa & " " & MyCurrencyDecimals & "s Only"
End Select
Else
Select Case Paisa
Case ""
Paisa = " Only"
Case "One"
Paisa = " and " & MyCurrencyDecimals & " One " & " Only"
Case Else
Paisa = " and " & MyCurrencyDecimals & "s " & Paisa & " Only"
End Select
End If

SpellCurr = Rupees & Paisa

End Function

'*******************************************
' Converts a number from 100-999 into text *
'*******************************************

Function GetHundreds(ByVal MyNumber)
Dim Result As String
If Val(MyNumber) = 0 Then Exit Function
MyNumber = Right("000" & MyNumber, 3)
' Convert the hundreds place.
If Mid(MyNumber, 1, 1) <> "0" Then
Result = GetDigit(Mid(MyNumber, 1, 1)) & " Hundred "
End If

' Convert the tens and ones place.
If Mid(MyNumber, 2, 1) <> "0" Then
Result = Result & GetTens(Mid(MyNumber, 2))
Else
Result = Result & GetDigit(Mid(MyNumber, 3))
End If
GetHundreds = Result
End Function

'*********************************************
' Converts a number from 10 to 99 into text. *
'*********************************************
Function GetTens(TensText)

Dim Result As String
Result = "" ' Null out the temporary function value.
If Val(Left(TensText, 1)) = 1 Then ' If value between 10-19...
Select Case Val(TensText)
Case 10: Result = "Ten"
Case 11: Result = "Eleven"
Case 12: Result = "Twelve"
Case 13: Result = "Thirteen"
Case 14: Result = "Fourteen"
Case 15: Result = "Fifteen"
Case 16: Result = "Sixteen"
Case 17: Result = "Seventeen"
Case 18: Result = "Eighteen"
Case 19: Result = "Nineteen"
Case Else
End Select
Else ' If value between 20-99...
Select Case Val(Left(TensText, 1))
Case 2: Result = "Twenty "
Case 3: Result = "Thirty "
Case 4: Result = "Forty "
Case 5: Result = "Fifty "
Case 6: Result = "Sixty "
Case 7: Result = "Seventy "
Case 8: Result = "Eighty "
Case 9: Result = "Ninety "
Case Else
End Select

Result = Result & GetDigit _
(Right(TensText, 1)) ' Retrieve ones place.
End If
GetTens = Result
End Function

'*******************************************
' Converts a number from 1 to 9 into text. *
'*******************************************

Function GetDigit(Digit)
Select Case Val(Digit)
Case 1: GetDigit = "One"
Case 2: GetDigit = "Two"
Case 3: GetDigit = "Three"
Case 4: GetDigit = "Four"
Case 5: GetDigit = "Five"
Case 6: GetDigit = "Six"
Case 7: GetDigit = "Seven"
Case 8: GetDigit = "Eight"
Case 9: GetDigit = "Nine"
Case Else: GetDigit = ""
End Select
End Function


Spell Currency Excel Addin avilable now



Numbers to Words , Convert Number to Words , Convert Number to Word , Number to Words , Number to Word , Number to Text , Number in Words , Number to Letters , Convert Number to Text , VBA Number to Text , Number to Text Function , Numeric to Text

301 comments:

  1. I found one more code by Yogi Anand, which follows indian system of Rs in lacs, crores and arabs. Checkout following link for the same.

    Yogi Anands Solution

    ReplyDelete
  2. Sir can you make changes for eg. if i type 150000. it should show one lakh and fifty thousand rupees only, now with your program its showing Rupees one hundred fifty thousand only, How can i make changes please upload the file if you have it,
    thank you

    ReplyDelete
  3. Replies
    1. But it's not working for other excel file, i mean i have saved your spellcurr add ins which helps to convert the numbers into text in other excel sheets also.

      Delete
  4. Thank you, for your Reply Anandji, Sir do you have any format in which if i type a) the company name should display, eg, if a) letter will represent Asain Paints, b) will represent Berger Paint c) for Esdee paints and so on.. and if i type a) the company Asain paints must display, and if b) Berger paints and so on... Do you have any format code, It will be easy to save company names at once instead of typying everytime,

    ReplyDelete
  5. Hi JPTC - What do you need can simply be achieved by Vlookup formula. Let me know if you need to know more about using Vlookup formula.

    Thanks
    Yogesh Gupta

    ReplyDelete
  6. Once Again thank you for your help, Vlookup Formula worked very good for me, Now the problem is about printing How to block printing of a specified cell only.
    For Eg if i dont want to print contains of cell no A1 and want the text to editable and visible on sheet then what is the formula, Can you help out

    ReplyDelete
  7. Hi JTPC

    Simple way will be to keep you Cell outside the print range. This way you can avoid printing it. However there are some tricks available where you can avoid printing certain cell or range. In such cases the content will be visible only in the formula bar. Wait for my next post to learn more about this.

    Thanks
    Yogesh Gupta

    ReplyDelete
  8. Hi JTPC

    Checkout my post on
    Hide Cell contents while printing. This will answer your question.

    ReplyDelete
  9. Dear Sir,

    I want to learn VBA for excel .So could you please guide me what kind of course should i pursue. i am searching for course related to Macros and VBA in excel.

    Regards
    B.N.Rath

    ReplyDelete
  10. Dear B.N. Rath

    Welcome to my blog. My practical advice to you is to start coding by recording macros. You actually do not need to remember lots of things which can be achieved by recording your action. When ever you are struck with the thing, record you action into a new macro and take out relevant VBA instructions from recorded macro.

    Go through Julian's website. http://www.angelfire.com/biz7/julian_s/julian/julians_macros.htm . This one has small small codes on various thing you need to do with VBA. This will help you a lot.
    I actually started with this one about a year back and started coding for all my routine jobs to save time

    Thanks
    Yogesh Gupta

    ReplyDelete
  11. Thank you sir for your valuable help by using your VB code data thank you againg

    ReplyDelete
  12. Thank you sir for your valuable help by using your VB code data thank you againg

    ReplyDelete
  13. Hello Jegs

    Welcome to my blog

    Regards//Yogesh Gupta

    ReplyDelete
  14. hello sir i have one problem in excel
    first of all thank you for reply me sir!
    my problem is i have a one excel invoice printing format workbook file and its have 70 working sheet, one sheet for each party with its detail.
    i need solution about saving each entry only data not whole invoice just need i.e invoice type, invoice no, date, party neme, product, qty, & amount only eight feed how can i do can any formula for this problem please help me out
    please give me solution

    ReplyDelete
  15. Hello Jegs

    You need to simplify your worksheet. Following are the steps you need.

    1. You do not need 70 sheets for Invoice. At best you will need 4 worksheets
    Sheet1 - As Invoice Template
    Sheet2 - Customer Master with all Details give unique customer code to each
    Sheet3 - Product Master with all details. Give unique product code to each.
    Sheet4 - Invoice Data base. You can have top row linked to Invoce Template. The data in the top row will update the details into Invoice sheet that can be printed. Only customer code and product code, qty, value and date etc to be updated in Invoice Data base sheet, rest of the details will be picked up from Customer Master and Product Master.

    Save that top row on the data base before making new invoice.

    I hope this will help you to have clean workbook with the searchable data base for your further reporting etc.

    Let me know in case you have any further questions.

    Regards//Yogesh Gupta

    ReplyDelete
  16. I am littelmuch confused about this database sheet? if yoy have any similar project excel sheet than please give me that, for example so that i can use the way of saving data

    ReplyDelete
  17. Hello Jegs

    Send me your worksheet, I will send simplified version back to you.

    Regards//Yogesh Gupta

    ReplyDelete
  18. where do i send it to you? in here or? please give my your email address

    ReplyDelete
  19. Send me at Yogesh @ yogeshguptaonline . com

    ReplyDelete
  20. thank you
    ok i will understand what you mean
    thanks again its very good

    ReplyDelete
  21. Dear Yogesh Gupta,

    I am really happy to find out this web portal which is really helpful for excel users. I am Abdul Qadeer from Islamabad and working in an Oil & Gas Company.

    My question is how can i convert numeric value into text while using vlookup. I have entered 1-99 in one column and text of all 1-99 in second column. This works well with this formula =(VLOOKUP(B19,M1:N99,2)). it shows text of numbers ranging from 1-99. Now i want to learn how can i convert numbers onward 100.

    Thank you for your service and I really appreciate your service for excel users.

    Thanks & kind regards,

    Abdul Qadeer
    Islamabad, Pakistan

    ReplyDelete
  22. Dear Abdul

    First of all Welcome to my blog.

    I can see that you are using Vlookup to get the word equivalant of a number. However Vlookup will not be able to construct the numbers for you. It can only provide the value against the lookup value from a data table. If you want to use this for numbers 100 onwards, you need to have data table of word values against each number.


    I noticed that Vlookup formula used by you will give only approximate result to you. For getting exact match you need to modify your formula =(VLOOKUP(B19,M1:N99,2,0)). Pls take note of ",0" added to your formula for getting exact match.

    You can download Excel file with VBA Function that can convert any number to words. This function will not handle faractions and all fractions will be rounded off before converting them to words. There are no prefix of sufix in this. I suggest you to use this custom function to convert numbers to words. Vlookup is not a good idea for this requirement.

    Regards//Yogesh Gupta

    ReplyDelete
  23. Hello yogesh sir,
    i ve made a indian bank check printing file in M.S WORD ,with the help of tools of exel. but i have to write all the numeric words eg- rs50000 and also text word like- fifty thousand only. is there any trick that text words comes automatically when i write numeric word. [in m.s word]. Please Help

    ReplyDelete
  24. Hello Gaurav

    Welcome to my blog. I am not aware of any method to do in MS Word.

    However you can think of setting up you cheque printing file is MS Excel. I hope this will be much easier than looking for a way to convert numbers to words in MS Word.

    If you are willing to change to MS Excel then the method to convert numbers to words is available here.

    Thanks
    Yogesh Gupta

    ReplyDelete
  25. hello yogesh sir,
    i worked in your exel file converter.[numeric to text] and its worked also. but the text line is coming in a one single line . but there are two lines of amount in every bank cheque. is there any idea to stop the first- amount line where i need and continue same text to second line.
    Thanks Sir.

    ReplyDelete
  26. Hello Gaurav

    I agree that there are two lines and there may be a case when first line is not enough to accomodate the entire text line.

    You can use wrap text option in excel to accomodate that in next line. I hope this is simple enough but let me know in case you need help
    Regards
    Yogesh Gupta

    ReplyDelete
  27. Hello sir,
    sir where i can find this[warp text] option in exel? will it be in my hand where to start in first line and conitinue in second line to end point?
    Thanks for helping me.

    ReplyDelete
  28. and sir how can i add indian style comma farmula in my exel file so that my my amount can also be seeing in commma style. i dont know how to add farmula and where to add?.

    ReplyDelete
  29. Hello Gaurav

    Pls look at the link on how to wrap text in excel

    For Indian Style comma format, please look at my post on India style comma separation This has detailed instructions. In case you find them difficult to follow, please email your file to me at Yogesh @ yogeshguptaonline . com

    Regards//Yogesh Gupta

    ReplyDelete
  30. Hello Yogesh Sir,
    My File Is Running So Good Now. Thanks For This Kind Help. so Help Full Person You Are.
    Regards
    Gaurav.

    ReplyDelete
  31. 2. sir, is there any trick that when i enter name of person/comapny name in the cheque file . it shows all alpabatically wise . like i have 10 comapny name in alpabat[ G] 1. Gian Chand Gobind Ram 2. Ganesh ji trading co. .... . means to say that once i save all the name some where in the exel file options and when i press G or A etc .. my file could display all the company of name of G and i will choose the name from display list.

    ReplyDelete
  32. Hello Sir
    I still confused
    how to write sum in numeric ??
    plz explain in a simple method sir

    Ishaq Khan

    ReplyDelete
  33. Hello Ishaq

    I am not clear about what do you want. Can you please elaborate further

    Thanks
    Yogesh Gupta

    ReplyDelete
  34. Dear Mr.Yogesh Gupta

    Hi. My name is K.V.Dhananjay. I am an advocate at the Supreme Court. I wish to thank you for your excellent help.

    Regards
    K.V.Dhananjay

    ReplyDelete
  35. Hi im Jasper Miral. sir can you help me in my problem in ms excel 2003. its about formula.

    example:
    _______________________________________________
    Account A B C D E TOTAL
    AMOUNT 500 200 300 200 500 1700
    PAYMENT 1 500 200 100 800
    BALANCE xxx xxx 200 200 500 900





    _______________________
    I RECEIPT I
    I Name : Sample I
    I amount: 800 I
    I I
    I A -formula I------->500
    I B -formula I------->200
    I C -formula I------->100
    I D -formula I
    I E -formula I
    I TOTAL -formula I------->800
    I_____________________I

    Sir is there any formula that when i enter payment amount it will automatically distribute to the listed account? example if the payment is 800, automatic 500 of that will go to account A, 200 to B and 100 to C, since there is only 500 listed on A account 200 on B and so on... But if the payment is 400, it will be posted only on account A and have a balance of 100.

    I hope you can help me. Thanks

    ReplyDelete
  36. Hi im Jasper Miral. sir can you help me in my problem in ms excel 2003. its about formula.

    example:
    _______________________________________________
    Account A B C D E TOTAL
    AMOUNT 500 200 300 200 500 1700
    PAYMENT 1 500 200 100 800
    BALANCE xxx xxx 200 200 500 900





    _______________________
    I RECEIPT I
    I Name : Sample I
    I amount: 800 I
    I I
    I A -formula I------->500
    I B -formula I------->200
    I C -formula I------->100
    I D -formula I
    I E -formula I
    I TOTAL -formula I------->800
    I_____________________ I

    Sir is there any formula that when i enter payment amount it will automatically distribute to the listed account? example if the payment is 800, automatic 500 of that will go to account A, 200 to B and 100 to C, since there is only 500 listed on A account 200 on B and so on... But if the payment is 400, it will be posted only on account A and have a balance of 100.

    I hope you can help me. Thanks

    please send it to my email
    jasper_miral@yahoo.com

    ReplyDelete
  37. Hi Jasper

    I am not able to see any solution through the formula, however this can be done with Macros.

    If you can send me your file , I can workout some macro for you. Send it to yogesh at yogeshguptaonline dot com

    Thanks
    Yogesh Gupta

    ReplyDelete
  38. Dear Yogesh Ji

    Very Good Evening to you. My name is vaibhav gupta & i had used spellnum & spell indian function, I am very impressed thanks alot.

    Yogesh I have an request I had read that you suggested someone using 4 sheet invoice data I request you to kindly forward me the template if possible for you. I will be greatful.
    my id is vaibhav1977@gmail.com

    Thanks & Regards
    Vaibhav Gupta

    ReplyDelete
  39. Dear Yogesh
    can we convert numbers in other language like portuguese
    if yes can you give me the formula on mukeemkhan@hotmail.com

    ReplyDelete
  40. @Vaibhav - I do not have template as asked by you. However I can help you if you send me your invoice template with some data there.

    @Mukeem aks Super - I do not have portuguese formula.

    Regards//Yogesh Gupta

    ReplyDelete
  41. Dear Yogesh Ji

    i am using the copy & paste method, i.e. coping the bill & pasting it below the existing bill. i had no idea how to create the links for picking up the data from the other other sheets with the codes assign to various customers, items & vendors. that's the only reason i had asked u to send me the template.

    ReplyDelete
  42. i want to convert values in to indian format like carore, lakhs etc is it possible ?

    ReplyDelete
  43. Dear Sir,

    How can modify the above so that "cents" can appear before the words..?
    eg. One hundred and cents fifty only

    Thanks

    ReplyDelete
  44. Good After Noon Sir,

    I read all of your suggestion and answer given to different person's .

    i would like to thank for your valuable answer over Excel which is very helpful to anybody who needs to learn something extra in excel.

    ReplyDelete
  45. This comment has been removed by the author.

    ReplyDelete
  46. Dear Mr. Yogesh Gupta

    This is Mahesh Jagtap. I tried to download your file provided for converting numbers into word format for Indian Rupees. But it was not responding. It would be a great help on your part if you mail the the same file to me on girish_1811@yahoo.com

    Thank you Sir.

    ReplyDelete
  47. Sir

    I have downloaded Shri Yogi Anand excel file to convert numbers to words as per Indian Style. I am totally new to excel.. kindly tell me as to how I can make those macros applicable my excel functions so that I can use the same in any of my excel work.. My email id is iyervsv@gmail.com

    Thanks in advance

    regards

    V S Venkatraman

    ReplyDelete
  48. Hi,

    Could you please tell me that how can i use this formula in my pc. I'm putting the formula but my system no taking it.... so please help to resolved it.

    Please mail me on my ID as:-
    dpandey.nsn@gmail.com

    Thanks
    Regards
    Dinesh

    ReplyDelete
  49. @ Mahesh, Venkat and Dinesh

    Instructions on usage of the formula are available in my post above. You will need to download the file with macro and deploy it to your file where you need to use it.

    For instructions refer to my post on how to deploy macros found in another workbook
    Regards

    ReplyDelete
  50. @ Yen

    You can choose the placement of currency decimals. Use formula as below

    =SpellCurr($A$1,"Dollar","S","Cent","P")

    In this case your number is in Cell A1 , change the reference as per postion on your worksheet

    Regards

    ReplyDelete
  51. @ Pravin - you can convert numbers to indian format like carore, lakhs etc. Please check the downloadable file available in downlowds sectgion of my blog.

    Regards

    ReplyDelete
  52. Dear Yogesh Sir,

    Would firstly like to thank you for running such a wonderful and informative blog.
    It has been of immense help for me to do my regular work.

    I have attached 3 files
    1) Invoice File
    2) Customer DB
    3) Sales Details

    Now i need a certain help from you.

    Firstly i need invoice file should directly fil in customer details form the customer Database
    i.e. Customer Name, Address (all 1, 2,3) and City and Pincode.

    Secondly i need that all the invoices made are automatically updated in the sales details file.
    I maintain seprate invoice files for each invoice.

    Please help me for the following queries.

    Waiting for your positive feedback

    Sandesh Jain
    email sent on 24/04/2010

    ReplyDelete
  53. dear sir, thanks for your valueable tips for all, sir i am working in oman , here currency is OMANI RIYALS.

    ONE OMANI RIYAL = 1000 BAISA.

    i modified IRS to OMANI RIYALS and PAISA to baisa,

    but convertion of omani riyal into baisa , i don't know kindly help.
    eg: when i write in cell 101.250 then result showing one hundred one riyals and baisa 25 only, main problum is baisa not convert in three digit,


    Thanks & regards


    Harish nebhwani

    harishnebhwani@rediffmail.com

    ReplyDelete
  54. Hi Harish

    You need to replace the following lines of the code

    Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "00", 2))

    With

    Paisa = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "000", 3))

    After this change in the code, you will be able to convert your number to 3 decimals of baisa.

    Hope you will be able to make this change, let me know if you need furhter help.

    Regards//Yogesh Gupta

    ReplyDelete
  55. Yogesh sir m waiting for ur reply pls. do reply have u got my mail.
    Sandesh Jain

    ReplyDelete
  56. Hi Sandeep

    I have not received any mail from you.

    Regards

    ReplyDelete
  57. sir if u can give me your current email address i will mail it to u again. i had earlier mailed to Yogesh@yogeshguptaonline.com.
    Thanks
    Sandesh Jain

    ReplyDelete
  58. Good Day,

    I'm having trouble making a formula for my sheet. I have managed to make a drop box, with Text words. I would like to put a Numeric value equaled to each text word which would appear on the next cell.

    Example

    a = 100
    b = 200

    if i choose "a" in the drop box the number 100 will appear on the cell,same goes if i choose "b"...

    I would really appreciate your help.

    Thanks

    ReplyDelete
  59. Dear Mr. Yogesh,

    I must put a word of compliment here for your emaculate work towards helping so many people solve their problems.

    I would request you to please send:-

    Sheet1 - As Invoice Template
    Sheet2 - Customer Master with all Details give unique customer code to each
    Sheet3 - Product Master with all details. Give unique product code to each.
    Sheet4 - Invoice Data base. You can have top row linked to Invoce Template.

    to my email address. I am in deep need for this at the moment.

    My email address is kumar74_P@yahoo.ca

    Thanks,

    Prashant

    ReplyDelete
  60. Sir,
    I used the formula "SpellCurr" it is amazing and works like charms but here is a problem that I have been facing e.g.
    I designed excel sheet as per format of bank cheque for cheques printing when I enter amount in figure it automatically turned that figure in words also. But problem remains there that the line of cheque is shorter than the line of excel sheet so the converted figure goes in long in that is larger than cheques' line.
    I want it that converted amount should be print in two lines half on cheques upper line where line finishes the remaining should be printed on the second line automatically.
    Please guide me here with this problem.
    Thanks + Regards
    Ali Khan

    ReplyDelete
  61. Dear Sir,,

    How get my Computer Fomula "Spell Curr"
    Please Help Me,,,,,,

    ReplyDelete
  62. Dear Mr. Yogesh,
    I have a problem this formula. how do i want make 10.096 spell as ten dollars and ten cents only, because it come out as ten dollars and nine cents only.

    ReplyDelete
  63. Hi Ali
    I will suggest you wrap text with in the cell using format options. Keep the columns width equal to the length of first line on your check,, this will wrap additional text to next line.
    Hope this is clear.
    Regards

    ReplyDelete
  64. Hi riezall

    I suggest you to use spellcurr formula in combination with round formula. The combined formula will be as under :-
    =SpellCurr(ROUND(A1,2),"Dollar","S","Cent","S")

    This considers your value of 10.096 is in cell A1.
    Regards

    ReplyDelete
  65. thanks Mr. Yogesh, it works!!

    ReplyDelete
  66. This comment has been removed by the author.

    ReplyDelete
  67. Hi am using SpellOmanRial it is very good and its helping me a lot for making in cheque making i have a problem, we used to spell One Hundred Thousand in the place of One lakh i changed lakh into Thousand and i adjust the 3rd space now if i typing 123456.395 it will come like One hundred Twenty Three thousand and Baizas three Hundred Ninety Five but if am typing 100000.000 its coming only One Hundred, Thousand is not coming at this case, can you please help me to solve this

    ReplyDelete
  68. Hi Sudheer,

    I am not sure which code you are using. I suggest you following changes in the code available on my blog.

    Replace the following lines of the code

    Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "00", 2))

    With

    Paisa = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1) & _
    "000", 3))

    After this change in the code, you will be able to convert your number to 3 decimals.

    Regards

    ReplyDelete
  69. Thanku for your earlier responds and help with warm regards

    ReplyDelete
  70. Hi Sir Sudheer with you i changed as per u r instruction but still the problem is there. Only if i have to make One lakh facing this problem. If am typing 100000.000 it comes " One hundred " it should be "One hundred Thousand " if you gets time pls suggest me .thnks & regards

    ReplyDelete
  71. Hello Mr. Yogesh,

    You seem to be responding to all other requests except mine.

    I really need the Invoice template, as it will ease out my work.

    I am posting my earlier request. I will really appreciate, if you could send the solution....

    Dear Mr. Yogesh,

    I must put a word of compliment here for your emaculate work towards helping so many people solve their problems.

    I would request you to please send:-

    Sheet1 - As Invoice Template
    Sheet2 - Customer Master with all Details give unique customer code to each
    Sheet3 - Product Master with all details. Give unique product code to each.
    Sheet4 - Invoice Data base. You can have top row linked to Invoce Template.

    to my email address. I am in deep need for this at the moment.

    My email address is noidadiesel@gmail.com

    Thanks,

    Prashant

    ReplyDelete
  72. Dear Prashant

    I do not have any such template, I do not know where and why you picked up this topic of sending the template to you.

    I may have suggested some one how to build this but I do not have this.

    I am planning to make one standard template but not getting enough time to do so.

    Regards//Yogesh Gupta

    ReplyDelete
  73. Hi Sudheer

    The change suggested by me will not work in the code available with you as I do not have any knowledge of the code available with you.

    Once again I suggest that you use the code available on my blog and change the lines as refered earlier to you.

    Regards//Yogesh Gupta

    ReplyDelete
  74. Refers to the above post posted on

    (Code for Indian Style number to text is available from Yogi Anand
    You can download Excel file with this code
    NOVEMBER 5, 2009 10:23 AM)

    Dear Sir,

    Is that possible for "SpellIndian" function the below code written for "SpellCurr" function.

    =SpellCurr($A$1,"Dollar","S","Cent","P")

    I mean I need =SpellIndian($A$1,"Dollar","S","Cent","P")

    Please comment on the same. As i tried to modify both the module of SpellIndian and SpellCurr but could not success.

    Please help.

    ReplyDelete
  75. Hi Mehul

    I am not clear that why do you want this change. I hope the SpellCurr function should be able to handle your requirement.

    Can you please share your objective with the results you want to obtain.

    Regards

    ReplyDelete
  76. Dear Sir
    First of all I heartily thank you for your reviews.

    I have one master file where we get the payment in USD and we make bills in Indian rupees multiplying with exchange rate.

    So one of our file required USD in words and INR in words. Now with the help of SpellIndian function i manage to convert into INR but same is not applicable for USD value which actually SpellCurr function do. But in SpellCurr function I am not getting INR conversation.

    So this is the reason I need both the facility in SpellIndian function.

    Please advise. Awaiting your reply.

    ReplyDelete
  77. Hi Mehul

    =SpellCurr($A$1) will convert value at cell A1 to words in Indian Rupee. This will use Millions format as we do in USD conversion.

    However if you need Rs in Lacs format, than you can use both the functions in your file.

    Convert Rupees by using SpellIndian and Dollars by using SpellCurr. There is no such limit that you can use only one function in your file. I will advise you to use both the functions to serve your needs.

    Regards

    ReplyDelete
  78. Dear Sir,

    Thanks you very much for the guidance.

    I have created two modules and now its work.

    Can I have one more help? Please

    I manage to convert into dollars and the value comes in 100 = One Hundred US Dollars Only. Can I get this 100 = US Dollars One Hundred Only.

    Please advise the changes in SpellCur function.

    Awaiting your valuable comments.

    With best regards

    MEHULKUMAR

    ReplyDelete
  79. Dear Sir,

    I have master file with your spellindian and spellcur function. Please advise how to lock the coding so that other can not see or edit the function.

    Awaiting your valuable comment

    MEHULKUMAR

    ReplyDelete
  80. Hello Yogesh Ji.

    How R U???

    First of all i would like to thank you for providing so many useful informations & tips.

    Yogesh ji I had a request, can you please help me in generating a customized data entry form in excel. coz the default form which excel provides supports very less fields.

    awaiting for your precious reply

    Thanks & Regards
    Vaibhav Gupta

    ReplyDelete
  81. sir, iam not understood how to convert numbers into words pl. explain

    ReplyDelete
  82. Dear Sir,

    I have master file with your spellindian and spellcur function. Please advise how to lock the coding so that other can not see or edit the function.

    Awaiting your valuable comment

    MEHULKUMAR

    ReplyDelete
  83. Hi Mehul

    You can protect your VBA code with the steps given at following link

    http://www.ozgrid.com/VBA/protect-vba-code.htm

    Regards//Yogesh Gupta

    ReplyDelete
  84. Hello Sir, Abhishek here,
    So nice to se your VBA codes......
    I need hardware locking in one of my excel file....How this could be possible...

    I tried ur VBA code but, it is not working.....
    Excxel function for Motherboard serial no. is not returning any string....I saw it by Msgbox
    could u plz help me out.

    ReplyDelete
  85. This comment has been removed by the author.

    ReplyDelete
  86. Hi Sir

    I have downloaded the "spell currency" file but would prefer converting numbers to indian format like crore, lakhs etc. After referring to your post

    " Yogesh Gupta said...
    @ Pravin - you can convert numbers to indian format like carore, lakhs etc. Please check the downloadable file available in downlowds sectgion of my blog.

    Regards
    May 2, 2010 9:17 AM "

    , I have tried checking the downloadable file available in downloads section of your blog, but the link is not opening.

    I would like to confirm the download section.

    Is it the below mentioned link:

    http://in.yogeshguptaonline.com/resources/DL

    If yes, then it's not opening.

    I would be very thankful if you could paste the link of the "lakh crore" format file in the reply.

    Regards
    Ajit

    ReplyDelete
  87. Hi Ajit

    You can download file form following link

    http://in.yogeshguptaonline.com/resources/excel-macros/NumberstowordfunctionIndiasystembyYogiAnand.xls?attredirects=0

    However I have checked that downloads link in working fine. Not sure why do you have problem with the download section link on my blog.

    Regards//Yogesh Gupta

    ReplyDelete
  88. hi yogesh gupt

    i have a small problem when i use the fomula =SpellCurr($A$1,"Dollar","S","Cent","P") its not working in excel what i have to do...ho can i correct it please .tell me

    ReplyDelete
  89. Great work..! But how do i do it on Excel for Mac?

    ReplyDelete
  90. Hello, I am trying to extract data from another CSV file using an IFfunction. But the data that needs to be extracted is in a value form and needs to be converted to text. For example: B803 needs to be converted to 1st. Can you please lead me in the right direction?
    Thanks for your help in advance.

    ReplyDelete
  91. Hello Mr. Yogesh

    How r u ??

    I don't know why you didn't reply to my last post, but not an issue.

    Can u pls help me this time.

    kindly teach me how to lock a particular cell or a cluster of cells.

    Waiting for your reply..

    Vaibhav Gupta

    ReplyDelete
  92. Number to Word Function Gujarati Language me Nahi ho Sakta
    Agar Ham apko Gujarati Font aur one two jese gujarati me type Karke apko Mail Kare to aap Hame Gujarati Me Function Bana doge
    apke email id

    ReplyDelete
  93. Hi Mr. Yogesh,

    I need your help on "Convert numbers into words" function.
    Let's say the number is 26,190.00
    The function will print "Hundred Twenty Six Thousand One Hundread Ninety Dollars And No Cents"
    Is it possible not to print the first "Hundred" because it doesn't mean anything

    ReplyDelete
  94. Dear Mr. Yogesh,
    i have gone thru ur site. it quite interesting.
    appreciate your work especially on responding to everyone's email with patience

    and now i need one help from you? i.e.

    as per this blog"numbers to word" i need it in the below format.please email me (ushbasha@yahoo.com) or post it here

    Eg1: US Dollars forty six thousand and 50/100 only
    Eg2: Saudi Riyal Forty six thousand and 50/100 only

    can you please help me how write this

    Thanks,
    Syed Hassan Basha
    Dermabit Waterproofing Industries
    Kingdom of Saudi Arabia

    ReplyDelete
  95. Sir, i m planing to start a small business, I need your help for crating a simple bill entering format in MS XL. Inputs are,
    Product name, Retailer Name,Discount, Amount , Cash/ Credit.
    I need to get retailer wise output,
    Pls help me Sir,
    My Mail ID : sunusdharan@gmail.com

    ReplyDelete
  96. Dear Sir,
    I am Dhairya From India,
    I don't have VBA Module installed on my office computer.
    So i am not able to find this solution in my PC.
    In this case what do i do?
    Please Help me sir,isn't there any other software or alternate solution for CONVERSION OF FIGURES INTO WORDS IN EXCEL.

    ReplyDelete
  97. Dear Yogesh,
    I am extensively using the numbers to words. However in Bahrain our "paisa" is three digits - How do you achieve this? which line of code has to be modified?
    thanks
    charymk@gmail.com

    ReplyDelete
  98. SIR THANKS THANKS!! THANKS!! A MILLION!!! WOW, with a few tweaks it works! I've been trying to do this for 2 years...again, sir from Nashville, TN USA...I say THANK YOU_--_!!!!!!

    ReplyDelete
  99. DEAR YOGESH,

    I WISH TO ADD THE CURRENCY OMANI RIAL AND ITS LOWER DENOMINATION, WHICH IS BAIZA AND 1000 BAIZA = 01 OMANI RIAL, WHEREAS WHEN I USE YOUR FILE,FOR 12.500, IT SPELLS THE NUMBER AS OMANI RIAL TWELVE AND BAIZA FIFTY, WHICH ACTUALLY SHOULD INDICATE AS OMANI RIAL TWELVE AND BAIZA 500/1000; i.e the Rial part in Text, and the Baiza part, with unit in text and amount in numbers as per required format above. Also the currency unit should not be in plural. Can you support with this modification requested. I am in sales, and find this program of yours very useful in my work. Hope you would look into and provide with the needful.

    ReplyDelete
  100. Dear Mr. Yogesh,

    I have a problem and I've read all comments and searched the internet, but I can't find the solution.

    I would like to ask you if you can help me.

    I want to convert the amounts in words in Indonesian language. For the biggest part it workes already, but sometimes not...
    1 = satu (is correct)
    10= se puluh (NOT satu puluh)
    100= se ratus (NOT satu ratus)
    101= se ratus satu (NOT satu ratus satu)
    1000= Se ribu (NOT satu ribu)
    10.000= Se puluh ribu (NOT satu puluh ribu)
    100.000= se ratus ribu (NOT satu ratus ribu)

    AND the 100 (100-199) and the 1000 (1000-1999) series:
    101=se ratus satu
    120= se ratus dua puluh
    1200= se ribu dua ratus

    So summarized which has to be changed:
    The word "se" instead of "satu" (which both means "1") in following cases:
    10 (NOT for 11 or others)
    100-199
    1000-1999
    10.000-10.999
    100.000-199.999
    10.000.000-10.999.999
    100.000.000-100.999.999

    The Million digit is ok according to the standard:
    1.000.000 (satu juta; one million)

    Of course for the above mentioned numbers also when we need a million number:
    Example:
    1.100.000= satu juta se ratus ribu (which means:
    saty juta: one million
    se ratus: one hundred
    ribu: thousand)

    So only the first word "SE" instead of standard "SATU"



    Need to be changed

    Other numbers are correct, like:
    12.000=dua belas ribu
    20.000=dua puluh ribu
    21.000-dua puluh satu ribu (21=dua puluh satu, so not "dua puluh se")


    Somehow (I don't know why exactly) I managed to get the 100 correct. But the others are still wrong.

    I really don't know how I can manage it to change the VBA working correctly according above criteria.

    I am very sorry that I am not Indian but I really would appreciate if you can help me with this matter.
    You don't have to change all if it is too much work, but maybe you can help me with one or to criteria of series so I can do the rest.

    I would be very grateful and happy if you could help me.

    Like I said, I already tried everything but couldn't solve the problem by myself or help on the internet.

    Please let me know if I may send you the VBA in an excel sheet and your email address.

    Thanking very, very much in advance & warmest regards,

    Jules Piekema

    ReplyDelete
  101. Hello Yoegsh Sir,

    How r u ? I would like calculate interest in RD. hows it will work? please send me code on mail bairagimd@gmail.com...or please give me u r cell no.
    Mangesh

    ReplyDelete
  102. Dear sir,
    i need to convert numbers into words(amount in rupees),
    i used ur method, i copy paste code to reqular vba mode as per instructions given here,
    but when i save that excel file, the message appears as below

    the following features cannot be saved in macro-free workbooks
    * vb project
    to save a file with these features, click no, and choose macro enabled file in the file-type list.
    to continue saving as a macro-free workbook, click yes.

    also, whenever i save file, every time this message comes, & when i close file (with the formula spellcurr() & modification)& reopen it, formula doesn't work.
    please give me simple solution

    thanks
    ajay

    ReplyDelete
  103. Hi Yogesh,

    I am trying to put an auto date in my invoice file in excel. I have couple of problems.

    1. There are files where in i have to put nextday's date. I want it to be done automatically in following format in a cell:

    Date: Today()
    so that it reads "Date: 11-Mar-2011"

    The problem is that it works fine till i reach saturday. But on saturday the next day is sunday wherein i want it on monday.

    can you help me in this regards. I am using excel 2007. I have tried the weekday() function but it does not help.

    Thanks in advance,
    Ketan.

    ReplyDelete
  104. Dear sir
    how can made conditional Formatting with Blinking cell with specify Vallue??

    Regards,
    Gautam

    ReplyDelete
  105. Dear Sir , I Want In This Format :
    11,15,85,965.50 = Eleven Crore Fifteen Lac Eighty Five Thousand Nine Hundred Sixty Five Rupees and Fifty Paisa Only

    ReplyDelete
  106. Dear Respected Sir
    Asalam-o-Alaikum

    I am Nadeem ASlam ARain From Sakrand Sindh Pakistan

    i Have use this code to convert numeral into words , and it work. i like your project thanks for share this with us

    thanks regards

    Nadeem ASlam ARain
    www.facebook.com/arainskd
    arainskd@yahoo.com
    +92 300-3238-138 (M)
    +92 24-4322-108 (L)

    ReplyDelete
  107. Hi!! Yogesh!!
    This is Ramki. Usually I do get Purchase Orders from my customers.The PO no.s follow quasi Julian date system. By doing a small analysis, one could arrive at PO date, but it’s a time taking process for a laundry list. I give illustration of my concern below..
    If PO No.is 110456,then 110-90(Jan=31+Feb=28+Mar=31,i.e.days in a month)= 20th,April.If I have all PO nos. in a column, I require PO dates in another column by use of an excel formula. Please help.

    ReplyDelete
  108. Hi Ramki

    Use following formula
    =DATE(2010,12,31)+VALUE(LEFT(A1,3))

    This considers that your PO number is in cell A1, you will need to modify it accordingly.

    Regards//Yogesh Gupta

    ReplyDelete
  109. Dear Yogesh,
    Thanks!! It's working fine. Could you Pls explain the logic?

    ReplyDelete
  110. Dear Yogesh,
    Need your help again.
    Regularly I will get MIS about the closing stocks of my customers.To arrive at the sales I have to substract prev.day stock position from today's stk.position, but if stock received then sale=today sale-Prev.day sale, in toto no negative no.s.
    In this fashion, to get a month sale I have to use a formula "if(prev.day>today,prev.day-today,today-prev.day)and then add all columns for 31 days. This exercise is boring and cumbersome.
    Pls suggest one formula which do magic for me.
    Thanks!!!

    ReplyDelete
  111. Thank you VERY MUCH!!!
    EXCELLENT WORK!!!!!!

    ReplyDelete
  112. Hello sir,

    I need a small help from you,


    I want a macro which converts the number into word as per the cell format (i.e. if cell format is in USD i should get the amount in USD like that if i change the cell format into other currency the same shoulh reflect in the word aswell), please can you suggest me whether this is possible.

    Thanks & Regards,
    Sumanth

    ReplyDelete
  113. Sir how to set this 'Excel Functions : Convert Numbers into Words' every worksheet. i mean my all excel file and new excel file....

    ReplyDelete
  114. DEAR YOGESH JI, ONCE AGAIN I NEED YOUR HELP MY QUES IS:

    I HAVE A SMALL SHOP IN OMAN WHILE STOCK TAKE I FACE FOLLOWING PROBLUM:
    NIDO MILK POWDER 2.5 KG PACKING (6X1)BOOK QTY :100 CT 2 PCS.
    PHY QTY 95 CT 4 PCS.
    (AS PER SIMPLE CALCULATION RESULT IS -4CT 4 PCS, BUT IN EXCEL SHOWING -4.998. PLS HELP ME I NEED RESULT -4.004.

    THANKS & REGARDS

    HARISH

    ReplyDelete
  115. dear sir once i copy ur data in my work book of excel sheet it only working once only. when i open again it is not working. can i use it permanently

    ReplyDelete
  116. Mr. Gupta,
    Thank you for an excellent UDF it works a treat.
    One thing though, here in the UK the recognised format for Pound and Pence is e.g. 27 pounds and 25 Pence, NOT 27 Pounds and 25 Pences.
    Just thought you would like to know.
    Thanks again.

    ReplyDelete
  117. Thank you for an excellent UDF it works.
    One thing though, here in india we have recently launched rupee symbol and we need Rupee logo instead of rupee word please it possible

    ReplyDelete
  118. Hello Mr. Gupta,
    Thanks for help on amount to words. Its good. I have downloaded the excel file. But i do have one problem, how to use the same excel file for other jobs.
    ie how to save it...should it be saved as a templet or something else. Please reply back.
    My mail id is kalyaninagar.times@gmail.com.
    Thanks in advance.

    ReplyDelete
  119. SALAM ,, YOGESH BHAI.
    I SEND THE FOLLOWING MAIL TO YOUR PERSONAL MAIL ID , I DONT KNOW WHETER YOU GOT IT NOT, CAN YOU PLEASE DO THE NEEDUFL OR ADVICE FOR THE FOLLOWING REQUEST.

    --- On Tue, 21/6/11, Be My Friend wrote:

    > From: Be My Friend
    > Subject: Excel Tip
    > To: Yogesh @yogeshguptaonline.com
    > Date: Tuesday, 21 June, 2011, 1:06 PM
    > Dear Yogesh bhai,
    > SALAM,
    >
    > First of all i appreciate for your works and cooperation
    > for helping, even i utilised your advice given by you to Mr
    > Harish .( Following Advice )
    >
    > You need to replace the following lines of the code
    >
    > Paisa = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) &
    > _
    > "00", 2))
    >
    > With
    >
    > Paisa = GetHundreds(Left(Mid(MyNumber, DecimalPlace + 1)
    > & _
    > "000", 3))
    >
    > After this change in the code, you will be able to convert
    > your number to 3 decimals of baisa.
    >
    > Hope you will be able to make this change, let me know if
    > you need furhter help.
    >
    > BUT YOGES BHAI, IAM FROM INDIA BUT WORKING IN KINGDOM OF
    > BAHRAIN, Here is like that
    > For example : Total amount of spare parts is BD: 256.075.
    > IN WORDS WE HAVE TO MENTION LIKE THAT
    > " BAHRAIN DINAR TWO HUNDRED FIFTY SIX AND FILS 075 /
    > 1000.ONLY.
    >
    > I WANT TO HAVE LIKE THAT ONLY. CAN YOU PLEASE HELP ME.
    > Thank you.
    >
    >
    >
    >
    >
    > your faithfully
    > Be my friend
    > (Shaik Iqbal )

    IAM AWAIGING FOR YOUR RESPOND.. THANK YOU.

    ReplyDelete
  120. Guptaji, You have help everyone please me too. I tried to do it the procedure of value to text but its not success. I use ms excel for invoice printing and every time i need to change the in words field, totally bore. then i was search in google got your posted info become happy. Help me Guptaji.
    Dhanyawad

    ReplyDelete
  121. Respected Yogesh sir,
    i want to result date 16/05/1972 without using any sepretor in same cell. example i write a date without sepretor 16051972 and automatic convert to with sepretor 16/05/1972 (dd/mm/yyyy)in same cell. how can i? please help me.

    ReplyDelete
  122. is this command use on all document automaticly.if yes how?

    ReplyDelete
  123. sir i want no to convet in word so please help me.

    ReplyDelete
  124. dear yogesh ji ..
    i m not using this function into office (Excel) 10 & 7.
    so plz tell me how i use this function in MC. Excel 7 & 10 ..

    Thank u.

    S.K. Agrawal
    sandeepazh@gmail.com

    ReplyDelete
  125. Hello dear sir,


    i have not usu this formula to other excel file.......plz tell me about it. thax

    psdhillon007@gmail.com

    ReplyDelete
  126. hai mr yogesh gupta thanks a million for your exel figure converting to word formula i was checking for this very lot time back now i got from your side i really glad and very tx again
    By Najeeb Khan A

    ReplyDelete
  127. hello sir,i tried ur formula & of yogi anand in excel.it works .hats of u sir for this commendable job.now based on ur formula,i tried in punjabi language where the problem comes when we have to write 21,22,23 etc. as i hindi language.can u pl.guide me to come out of this problem.pl tell how to make cases for these based on yogi anand formula also.

    ReplyDelete
  128. DOWNLOAD NUMBER TO TEXT CURRENCY CONVERTER EXCEL ADDIN IN PUNJABI LANGUAGE FROM
    www.employeesforum.yolasite.com

    ReplyDelete
  129. want to convert numbers to words like 150000/- to rupees one lac fifty thousand only. i tried putting the code you have give it is not getting saved. please help me out

    ReplyDelete
  130. want to calculate time. we have vehicles on hire we need to calculate total hours used and extra hours above 8hrs. e.g. start time: 20/09/2011 11am end time: 20/09/2011 10pm total hrs is 11hrs & extra 3hrs. give me the formula to calculate time so that i should only insert start time & end time in a particular cell.

    ReplyDelete
  131. Dear Yogesh
    Please Assist on the below

    I want to convert Numeric Numbers to Words but even Fractions shall be Converted in words as Cents

    Please advise how to do it by Virtual Basic

    ReplyDelete
  132. This comment has been removed by the author.

    ReplyDelete
  133. Hi Yogesh,

    Thanks so much for your tips and for the codes you provided to force users to Enable Macros. That was very kind of you.

    One point though that I noticed, and for which I would personally require your further assistance is that when the workbook containing the sheets is opened, one is presented with the page containing the original Excel Security Warning of "Disable Macros", "Enable Macros" & "Further Info". At this point, if the user clicks 'Disable Macros', he/she is taken to Msg Page which your code provides. I discover that after reading the message which would say e.g. 'You Must Click Enable Macros To Work', the user has no option but to close the workbook and start all over again. Is there anyway your code could be tweaked so that if an 'OK' button is added at the buttom of the msg sheet, the user is taken back to the window that has the Excel Security Warning sheet so that the 'Enable Macros' button could then be clicked. It will prevent the clicking of the ‘X’ at top right corner of the msg sheet, and reopening the workbook all over again.

    If you can help with this, please, it would prevent the user having to close the workbook and start all over again.

    ReplyDelete
  134. very nice yogesh ji, your formula is really great and works as well.

    ReplyDelete
  135. hello dear yogesh ,
    i run a small business for which i made my invoice in excel 2007, but for data base i've made hyperlinks to which the customer records and products are stored but everytime i need to edit the product data for quantity and manually maintain the invoice sequence
    secondly we are starting a branch in other place ,so i want it open for the new branch but control here and what ever printed there should be saved here,
    kindly help me
    thanks
    osman pasha
    alainpharma@gmail.com

    ReplyDelete
  136. sir, i m using drop down & vlookup function but when i use drop down list it is working in sorting order can u help me this problum

    ReplyDelete
  137. Hello Sir,
    First i cordially want to say u thanx regarding your valuable help in excel,Sir i want to remove "rupees" word which print in your udf"spellcurr",weather it is possible?

    ReplyDelete
  138. Hi, im trying to use the SpellCurr formula but it is not working, can anyone please guide me

    ReplyDelete
  139. Hi

    Can any body help me to convert words to number
    Tayyab Hussain

    ReplyDelete
  140. please any one can send to me sheet allready have the function of converting no to text
    i need it badly for making invoices and bills
    my email is "Samuel.Bahgat@orascomhd.com"
    regards

    ReplyDelete
  141. Sir, could u plz tell how to use "Spellcurr" in whole Excel program.

    email id: katti.pa1@gmail.com

    ReplyDelete
  142. Sir we are facing problem in executing Spellcurr function in whole excel sheet.please guide us
    Email id:shahnirav65@gmail.com

    ReplyDelete
  143. I want to Numerical convert to Text exm.
    125.50 say one hundred twenty five rs. and 50 Paise only

    ReplyDelete
  144. Hello yogesh g.. and all other friends
    how to convert 15.50 lacs into word
    (Rupees fifteen lacs fifty thousand only)

    ReplyDelete
  145. Sir I Want the formula of convertor of number to word me send me the file or fomrula plz sir

    ReplyDelete
  146. Sir I Want the formula of convertor of number to word in excel please send me the file or fomrula plz sir
    Regards,
    Sachin
    sachinshelke_7@rediffmail.com

    ReplyDelete
  147. if i type 150,now with your program its showing Rupees one hundred fifty only,now i want to showing only 'one hundred fifty' How can i make changes please upload the file if you have it,
    thank you

    anish ranjan mondal
    anish_mondal@rediffmail.com

    ReplyDelete
  148. if i type 150,now with your program its showing Rupees one hundred fifty only,now i want to showing only 'one hundred fifty' How can i make changes please upload the file if you have it,
    thank you

    anish ranjan mondal
    anish_mondal@rediffmail.com

    ReplyDelete
  149. if i type 150,now with your program its showing Rupees one hundred fifty only,now i want to showing only 'one hundred fifty' How can i make changes please upload the file if you have it,
    thank you

    anish ranjan mondal
    anish_mondal@rediffmail.com

    ReplyDelete
  150. Use following formula

    =RIGHT(SpellCurr(A1),LEN(SpellCurr(A1))-7)

    This considers your value(150) is at Cell A1, you will need to change the formula accordingly

    ReplyDelete
  151. This comment has been removed by the author.

    ReplyDelete
  152. Dear Sir,

    I am using, the below code in Openoffice:-

    Code Link (I had to post a link, as it could not fit into a comment):
    http://www.excelforum.com/excel-worksheet-functions/674862-converting-numbers-money-into-words-2.html


    It works fine
    When I have an amount e.g.
    10,000.20 (i.e .20 Cents/Paise) - It reads (Rupees Ten Thousand and Twenty Paise Only)

    BUT
    When I have an amount e.g.
    10,000.00 (i.e .00 Cents/Paise) - It reads (Rupees Ten Thousand and Paise Only)
    Instead, I would like it to read
    (Rupees Ten Thousand and ZERO Paise Only)

    Could you please, help me achieve the above?

    Thanking you in advance,

    Tina Sains

    ReplyDelete
    Replies
    1. @Tina Sains , you can now download Excel Addin and install it on your machine. Checkout new post http://www.yogeshguptaonline.com/2012/02/excel-addins-spell-currency-excel-addin.html

      Delete
  153. Dear sir,
    Hello

    mujhe excel me Rupees (Exa. 1000 ka number & word me under rupees ki coding chahiye) please help mi.

    example---
    input=100
    output= 101 in number &
    word = under rupees- One Hundred one Only.

    ReplyDelete
    Replies
    1. 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) = "Fourty"
      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


      OPEN EXCEL PRESS ALT + F11
      NOW ADD A NEW MODULLE...PAST THE CODE
      PRESS ALT + Q TO SAVE THE FILE

      SAVE THE EXCEL FILE

      HOW TO USE THE FUNCTION


      OPEN FILE TYPE NUMERIC VALUE ON CELL A1 100
      NOW USE THE FUNCTION ---- TYPE NEXT CELL NTOW(A1)
      AND PRESS ENTER

      Delete
  154. SIR I WANT THE WORD IN LAKH PLE GIVE THE ADVISE

    VISHESH TYAGI

    ReplyDelete
  155. Dear sir,
    Hello

    mujhe excel me Rupees (Exa. 1000 ka number & word me under rupees ki coding chahiye) please help mi.
    Microsoft Office Excel 97-2003
    example---
    input=100
    output= 101 in number &
    word = under rupees- One Hundred one Only.
    Reply
    shivpalrajput@gmail.com

    ReplyDelete
    Replies
    1. 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) = "Fourty"
      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


      OPEN EXCEL PRESS ALT + F11
      NOW ADD A NEW MODULLE...PAST THE CODE
      PRESS ALT + Q TO SAVE THE FILE

      SAVE THE EXCEL FILE

      HOW TO USE THE FUNCTION


      OPEN FILE TYPE NUMERIC VALUE ON CELL A1 100
      NOW USE THE FUNCTION ---- TYPE NEXT CELL NTOW(A1)
      AND PRESS ENTER

      Delete
  156. I am Getting Hundred in place of LAKH...

    ReplyDelete
  157. Dear Sir i want to change number - 122529876 in word format in excel . but i am unable to change it. Kindly help me..

    ReplyDelete
  158. yogesh ji by seeing the above mails i am eager to learn such type of formulas and shortcuts. i was very much inspired by your work.
    Please give me suggestions to upgrade myself in excel. as i was working with excel from the past 3years.

    ReplyDelete
  159. Yogesh Sir,
    Please reply my above question.
    Urgent!

    ReplyDelete
  160. Hi. I am Ashish Choudhary. I tried using the code provided in this blog to convert number to word but I am getting error.

    I have followed the below procedure

    Copy code from the source
    -Open the workbook where you need to deploy this code
    -Press ALT+F11 (Keep ALT key pressed while pressing F11 key)
    -Click Insert > Module (Shortcut ALT+I+M)
    -Paste macro code Click Edit > Paste (Shortcut CTRL+V

    When I use the function =spellcurr(a1)I get the below mwntioned error

    #NAME?

    Pl help???

    ReplyDelete
    Replies
    1. @ Ashish , you can now download Excel Addin and install it on your machine. Checkout new post http://www.yogeshguptaonline.com/2012/02/excel-addins-spell-currency-excel-addin.html

      Delete
  161. Hi Yogesh,

    I am getting this error

    Compile Error:
    Invalid Outside Procedure.


    Please help

    ReplyDelete
    Replies
    1. @ sidharth , you can now download Excel Addin and install it on your machine. Checkout new post http://www.yogeshguptaonline.com/2012/02/excel-addins-spell-currency-excel-addin.html

      Delete
  162. Sir , me a teacher, need to convert students' scores (figure) into words(figure), will U help me please.

    ReplyDelete
    Replies
    1. Use following formula along with SpellCurr() addin to get the desired results

      =RIGHT(LEFT(spellcurr(A1),LEN(spellcurr(A1))-5),LEN(spellcurr(A1))-11)

      This considers that you have your score at Cell A1, you will need to change the formula accordingly

      Delete
    2. Hi Yogesh Bhai, Thank you for sharing your knowledge with others. I am in Bahrain and I am trying to get an output for 100.125 as One Hundred and Fils 125 only. Could you please help me to get this result

      Delete
  163. Dear sir,
    I have download file ffom your blog

    I think there is a problem in your file b,coz,
    when i am taking amount above 1 lac the file is showing
    exm- 127709
    then file is showing---Rupees One Hundred Twenty Seven Thousand Seven Hundred Nine Only
    like this
    pls help me

    Sachin salvi
    salvi.sachin1@gmail.com
    i am waiting for your ans

    Thanks

    ReplyDelete
  164. This comment has been removed by the author.

    ReplyDelete
  165. Hi Yogesh

    Many Many Many thanks for your coding, its Great with what i am doing for converting currency to text.

    I have one small issue -

    Your Code reads the Sum ($4,450.50) as :

    Four Thousand Four Hundred Fifty Dollars and Fifty Cents

    However i would like it to read :

    Four Thousand Four Hundred AND Fifty Dollars and Fifty Cents

    -----

    I tried editing the code and when i am down to double digit currency figures (80.69) the code reads :

    AND Eighty Dollars and sixty nine cents

    ---

    Any Assistance will help
    Umar

    ReplyDelete
  166. Dear Sir,

    I am working down in Nicaragua and am having trouble finding a code that converts numbers into spanish text.

    Please let me know if you have any thoughts on the matter

    SLDS.

    Mat
    gostanford24@yahoo.com

    ReplyDelete
  167. Dear Sir,

    I have an excel sheet in which birthdates of the employees are mentioned. I wish to run a macro to send emails to the particular employees email ids with referecne to the birthday = today()

    ReplyDelete
  168. Wow, Thanks!:) God Bless you..

    ReplyDelete
  169. Your formulas are great and was wondering if you could help.
    I need a formula that will convert a cell that has a word and then it would multiply by a + or -1.
    Basic formula I am trying to make is:
    if C1="Buy" then D1*E1(-1)
    if C1="Sell" then D1*E1*(1)

    This formula would be in the F1 cell

    C1 = Buy D1=50 E1=15.25 F1=(762.50)

    ReplyDelete
  170. Hi You Can Too
    Have a look at this file and see if it helps
    http://dl.dropbox.com/u/16092591/YouCanToo.xls

    ReplyDelete
  171. Hi Yogesh Bhai, Thank you for sharing your knowledge with others. I am in Bahrain and I am trying to get an output for 100.125 as One Hundred and Fils 125 only. Could you please help me to get this result

    ReplyDelete
  172. Sir can you make changes for eg. if i type 15.20 it should show Rs. Fifteen and Twenty Paise only, now with your program its showing Rupees fifteen and paise Twenty (Word "only" is missing),So Word only is to be added after it and Word "Paise" must be before amount (Just like Rs.Fifteen and Paise Twenty only)
    thank you

    ReplyDelete
  173. Hi Yogesh

    Many Many Many thanks for your coding, its Great with what i am doing for converting currency to text.

    I have one small issue -

    Your Code reads the Sum ($4,450.50) as :

    Four Thousand Four Hundred Fifty Dollars and Fifty Cents

    However i would like it to read :

    Four Thousand Four Hundred AND Fifty Dollars and Fifty Cents

    -----

    I tried editing the code and when i am down to double digit currency figures (80.69) the code reads :

    AND Eighty Dollars and sixty nine cents

    ---

    Any Assistance will help
    Umar
    Reply

    ReplyDelete
  174. Thanks a lot, It helped me a lot. But only one thing i noticed, it spells in thousands and millions rather than in lakhs or crores. Anything for this?

    ReplyDelete
  175. Sir,
    I am Dinesh working as Lecturer in Govt. College. I have one doubt sir. How to convert number to text in MS Office 2007 version excel sheet. Ex: 35 to THREE FIVE, 248 to TWO FOUR EIGHT. like this. I tried many times but getting error. Plz send one example excel sheet to my email ID sir.
    kumardineshg@gmail.com
    Plz help sir

    Regards
    Dinesh

    ReplyDelete
  176. Arvind, Sir how to add figure to word formula in excel 2010

    ReplyDelete
  177. Arvind, Sir how to add figure to word formula in excel 2010

    ReplyDelete
  178. Dear Sir,

    I have been searching for a way to extract cell values from my worksheet to be displayed in my desktop as a sidebar without actually opening the source file (worksheet) and will automatically update the value in the sidebar for a specified time. I am referring to something like a sidebar gadget that displays cell values in my excel file. Is there really a way to do this? There was a blog from MSDN by Cum Grano Salis about this but it doesn't work at all.

    I hope you know some way to do this.
    Thank you very much.

    Donex7

    ReplyDelete
  179. Sir, how to add figure to word formula in excel 2010 because alt+f11 is not working. please guide.

    ReplyDelete
  180. Dear sir yogesh Gupta ji

    I have some problem in excel i want to know about spellcurr formula can you suggest me sir. Thanx and regards Vishal Aulluck (Email: aulluckvishal@gmail.com

    ReplyDelete
  181. Dear sir yogesh Gupta ji

    I have some problem in excel i want to know about spellcurr formula can you suggest me sir. Thanx and regards Vishal Aulluck (Email: aulluckvishal@gmail.com

    ReplyDelete
    Replies
    1. Please download addin from download section of the blog.

      Delete