## 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.

### 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`

## 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

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

2. good sir, help full
thanks

3. Thank you!!!

4. 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

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.

5. 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,

6. 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

7. 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

8. 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

9. Hi JTPC

Checkout my post on

10. 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

11. 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

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

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

14. Hello Jegs

Welcome to my blog

Regards//Yogesh Gupta

15. 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

16. 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

17. 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

18. Hello Jegs

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

Regards//Yogesh Gupta

20. Send me at Yogesh @ yogeshguptaonline . com

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

22. 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.

Thanks & kind regards,

23. 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

24. 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

25. 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

26. 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.

27. 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

28. 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.

29. 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?.

30. 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

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

32. 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.

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

Ishaq Khan

34. Hello Ishaq

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

Thanks
Yogesh Gupta

35. 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

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

37. 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

38. 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

39. 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

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

41. @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

42. 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.

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

44. Dear Sir,

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

Thanks

45. Good After Noon Sir,

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.

46. This comment has been removed by the author.

47. 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.

48. 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

regards

V S Venkatraman

49. 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

50. @ 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

51. @ 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

52. @ 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

53. 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.

Sandesh Jain
email sent on 24/04/2010

54. 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

55. 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

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

57. Hi Sandeep

I have not received any mail from you.

Regards

58. 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

59. 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

60. 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.

Thanks,

Prashant

61. 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

62. Dear Sir,,

How get my Computer Fomula "Spell Curr"

63. 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.

64. 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

65. 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

66. thanks Mr. Yogesh, it works!!

67. This comment has been removed by the author.

68. 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

69. 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

70. Thanku for your earlier responds and help with warm regards

71. 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

72. 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.

Thanks,

Prashant

73. 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

74. 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

75. Refers to the above post posted on

(Code for Indian Style number to text is available from Yogi Anand
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.

76. 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

77. 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.

78. 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

79. 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.

With best regards

MEHULKUMAR

80. 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.

MEHULKUMAR

81. 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.

Thanks & Regards
Vaibhav Gupta

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

83. 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.

MEHULKUMAR

84. 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

85. 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.

86. This comment has been removed by the author.

87. 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 "

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

88. Hi Ajit

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

Regards//Yogesh Gupta

89. 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

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

91. 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?

92. 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.

Vaibhav Gupta

93. 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

94. 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

95. 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

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

96. Wonderful ...Sir

97. 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

98. 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.

99. 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

100. 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_--_!!!!!!

101. 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.

102. 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.

Thanking very, very much in advance & warmest regards,

Jules Piekema

103. 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

104. 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.

thanks
ajay

105. 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.

Ketan.

106. Dear sir

Regards,
Gautam

107. 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

108. thanx

109. 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

arainskd@yahoo.com
+92 300-3238-138 (M)
+92 24-4322-108 (L)

110. 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.

111. 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

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

113. Dear Yogesh,
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!!!

114. Thank you VERY MUCH!!!
EXCELLENT WORK!!!!!!

115. 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

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

117. 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

118. 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

119. 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.

120. 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

121. 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.

122. 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.
>
> Thank you.
>
>
>
>
>
> Be my friend
> (Shaik Iqbal )

IAM AWAIGING FOR YOUR RESPOND.. THANK YOU.

123. 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.

124. 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.

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

126. Thanx yogesh ji.

127. niceeeeeeeeeeeeeeee ji

129. 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

130. Hello dear sir,

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

psdhillon007@gmail.com

131. Thanks for Help

132. 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

133. 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.

www.employeesforum.yolasite.com

135. 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

136. 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.

137. Dear Yogesh

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

138. This comment has been removed by the author.

139. 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.

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

141. 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

142. 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

143. 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?

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

145. Hi

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

146. 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

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

email id: katti.pa1@gmail.com

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

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

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

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

152. 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

153. 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

154. 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

155. 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

156. Dear Yogesh Gupta,

I am really happy to find out this web portal which is really helpful for excel users. I am RAJESH from Hyderabad. I am using a macro code which is changing numbers to words, but here a deaufault.
when i close excel programe and reopen fresh excel page. and when i used same formulal. the formuula had been losed why.
pls give a code that run every time in excel.
my mail id villarajesh2020@gmail.com

157. 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

158. This comment has been removed by the author.

159. Dear Sir,

I am using, the below code in Openoffice:-

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)
(Rupees Ten Thousand and ZERO Paise Only)

Could you please, help me achieve the above?

Tina Sains

160. 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.

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

161. SIR I WANT THE WORD IN LAKH PLE GIVE THE ADVISE

VISHESH TYAGI

162. 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.
shivpalrajput@gmail.com

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

163. I am Getting Hundred in place of LAKH...

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

165. 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.

166. Yogesh Sir,
Urgent!

167. thanks Mr.Yogesh

168. 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???

169. Hi Yogesh,

I am getting this error

Compile Error:
Invalid Outside Procedure.

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

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

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

171. Dear sir,

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

172. This comment has been removed by the author.

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 -

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

174. 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

175. 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()

176. thanks buddy for convertors, was trying for currency.
its good

177. Wow, Thanks!:) God Bless you..

178. 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="Sell" then D1*E1*(1)

This formula would be in the F1 cell

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

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

180. Thank you very much

181. 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

182. 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

183. 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 -

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

184. 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?

185. 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

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

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

188. 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

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

190. 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