Convert a Numeric value to Words Without Vba
Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
Note : The following formula can convert amount one less than 1 trillion into words.
For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents
$1,250.50 is placed in cell B5.
Paste the following formula in cell C5.
source : http://www.listendata.com/2013/12/co...rds-using.html
Please Login or Register to view this content.
Last edited by HaroonSid; 06-01-2014 at 11:32 AM.
Use Code-Tags for showing your code :
Please mark your question Solved if there has been offered a solution that works fine for you
If You like solutions provided by anyone, feel free to add reputation using STAR *
That's one heck of a formula. Thanks for the effort.
i think this is largest formula
i think there is a function for that in excell itself
This is an awesome formula
Heres my contribution to the great work,
I have translated this into 2 more formats:
Standard Mexico Spanish Lettering (including the billions/milliards correction for spanish)
Standard Mexico Spanish Cheque formatting
I hope they are usefull to someone else, took me 2 hours to troubleshoot all the minute text bugs and wording nuances when converting to spanish, so enjoy (god i hate my language lol)
As a recomendation, use notepad or another text editor to replace N34 with the cell number you use on your sheet, theres lots of iterations in the formula, doing drag and drop on each one by hand will probably take a long time.
Standard Mexico Spanish Lettering:
$12,525,681.93
Shows as:
DOCE MILLON(ES), QUINIENTOS VEINTE Y CINCO MIL, SEISCIENTOS OCHENTA Y UN PESOS CON NOVENTA Y TRES CENTAVOS
Standard Mexico Spanish Cheque formatting:Please Login or Register to view this content.
$12,525,681.93
Shows as:
( DOCE MILLON(ES), QUINIENTOS VEINTE Y CINCO MIL, SEISCIENTOS OCHENTA Y UN PESOS 93/100 M.N. )
Please Login or Register to view this content.
Last edited by XionicFire; 03-01-2016 at 12:32 AM. Reason: Fixed Typo in one of the formulas
This is great, thanks for sharing!
Welcome@leyo
thanks
this very nice!!
Originally Posted by HaroonSid
Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
Note : The following formula can convert amount one less than 1 trillion into words.
For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents
$1,250.50 is placed in cell B5.
Paste the following formula in cell C5.
Please Login or Register to view this content.
Wow Haroon, thanks for sharing your codes if you generated it yourself from scratch!
Yours is the only solution for such conversion without vba.
Are we able to do it from:
One Thousand Two Hundred Fifty Dollars and Fifty-One Cents
to
One Thousand Two Hundred Fifty and Cents Fifty-One Only
Greetings from Singapore by the way!
Try this
and i did not make this codePlease Login or Register to view this content.
i mentioned above in my post
Thank you for your swift response!Try this
and i did not make this codePlease Login or Register to view this content.
i mentioned above in my post
For $1,250.51, with your above updated code, i got this:
One Thousand Two Hundred Fifty Dollars and Fifty-One Only
May I ask if you are able to churn out this instead?
One Thousand Two Hundred Fifty and Cents Fifty-One Only
The removal of word 'Dollars' and additional of Cents after the 'and' word
Much warm thanks!
balling89 welcome to the forum
Unfortunately your post does not comply with Rule 4 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
balling89 welcome to the forum
Unfortunately your post does not comply with Rule 4 of our Forum . Do not post a question in the thread of another member -- start your own thread.
If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.
Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.
My many apologies. Duly noted. Thank you for the kind heads-up.
looking for a indian currency version
Great Formula...
But what is recommended?
Using the VBA code conversion or using this formula in the cell, in terms of speed of the calculation.
Especially if the formula is being used for 10 times or more in the same spreadsheet?
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Hi,
It was helpful for me. But, I got a problem, when input data 132.68 in cell "B6" it shows #VALUE. would you please check the issue.
regards
rif
Note : It works for a value having two decimal places. It truncates if you have a value having more than two decimal places.
Note : The following formula can convert amount one less than 1 trillion into words.
For example : $ 1,250.50 = One Thousand Two Hundred Fifty Dollars and Fifty Cents
$1,250.50 is placed in cell B5.
Paste the following formula in cell C5.
source : http://www.listendata.com/2013/12/co...rds-using.html
Please Login or Register to view this content.
It's not working while i input 132.68 in cell "B6".
Hi,
I made a template about the subject before. I created a NumbertoText function in Module1(VBA Window).
The function can be used on all sheets of the workbook.
For example, enter "the number 50" into cell G1, and enter the following formula into another cell:
=NumbertoText(G1)
The result will be as this : Fifty Dollars and No Cents
kadr thanks for the input, but perhaps you missed reading the title properly - it specified withOUT using VBA
Re: Convert a Numeric value to Words Without Vba
Thanks a lot for this...
Asha Kanta Sharma
Guwahati, India
Hello Sir,
I need to spell currency number to text up to 3 decimal place without VBA. Please provide the formula.
Thank you.
Administrative Note:
Welcome to the forum.
We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.
Please see Forum Rule #4 about hijacking and start a new thread for your query.
If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks