Number in words
- Thread starter begcar
- Start date
I'd recommend taking the English version here:
http://support.microsoft.com/kb/213360
and translating the words from english to portuguese.
http://support.microsoft.com/kb/213360
and translating the words from english to portuguese.
Smallman
Excel Ninja
YBegcar
You could be the first person to come up with a formula to do this in portugese by adapting this formula.
I remember this formula very well as I made the mistake of not testing it before I commented on it. It is like Grease lightning and it is also the longest formula I have ever seen. I still scratch my head at its brilliant simplicity....
Just convert the Indian words to Portuguese (should only take 15 minutes).
http://www.ozgrid.com/forum/showthread.php?t=176326&p=681494#post681494
Take it easy
Smallman
You could be the first person to come up with a formula to do this in portugese by adapting this formula.
I remember this formula very well as I made the mistake of not testing it before I commented on it. It is like Grease lightning and it is also the longest formula I have ever seen. I still scratch my head at its brilliant simplicity....
Just convert the Indian words to Portuguese (should only take 15 minutes).
http://www.ozgrid.com/forum/showthread.php?t=176326&p=681494#post681494
Take it easy
Smallman
ThrottleWorks
Excel Ninja
Hi begcar, I'd suggest starting here as a reference, and then translating the words:
http://www.ozgrid.com/VBA/CurrencyToWords.htm
http://www.ozgrid.com/VBA/CurrencyToWords.htm
SirJB7
Excel Rōnin
Hi, begcar!
I mostly use Spanish Excel versions and sometimes have used English and French ones. The routine for converting numbers to letters (with currency formats) is widely available in all languages and in more than one variant.
I use the same old routine that I created years ago and that's been enhanced thru its life cycle, it's in Spanish of course. For English I borrowed one from I can't say where, or I'll get in trouble. For French I used another one downloaded after searching the web.
I think that it's not such a simple task to translate words from one language to another, there're connectors, endings, groupings and many things that turn a theoretically simple job of translation into a practical damned suffering punishment of rewriting. Perhaps those who haven't faced multilanguage conversion issues (like this of the numbers to letters) choose the above suggested path. IMHO, I'd discard it absolutely, unless you start from code in a language that is almost identical in grammar and syntax to Portuguese.
So my advice is to use google.com.br, download a few, test them all carefully, choose the most suitable... and keep it handy and update it as required, but always keeping backward compatibility (otherwise you'll end with many routines in the near future.
Regards!
I mostly use Spanish Excel versions and sometimes have used English and French ones. The routine for converting numbers to letters (with currency formats) is widely available in all languages and in more than one variant.
I use the same old routine that I created years ago and that's been enhanced thru its life cycle, it's in Spanish of course. For English I borrowed one from I can't say where, or I'll get in trouble. For French I used another one downloaded after searching the web.
I think that it's not such a simple task to translate words from one language to another, there're connectors, endings, groupings and many things that turn a theoretically simple job of translation into a practical damned suffering punishment of rewriting. Perhaps those who haven't faced multilanguage conversion issues (like this of the numbers to letters) choose the above suggested path. IMHO, I'd discard it absolutely, unless you start from code in a language that is almost identical in grammar and syntax to Portuguese.
So my advice is to use google.com.br, download a few, test them all carefully, choose the most suitable... and keep it handy and update it as required, but always keeping backward compatibility (otherwise you'll end with many routines in the near future.
Regards!
Smallman
Excel Ninja
Bob
Did you read my first post. I believe it is post 4 above!!!
That is where the initial comment came from. I made the erroneous assumption that a formula of that length would take up a lot of memory and not work very well. I was wrong - like I am on many things and if you follow the thread on Ozgrid I happily admitted to my mistake. At any rate I think it is wise to change your mind as you are shown a better way or are proven to be wrong. It won't be the last time but I will keep trying to learn from my mistakes.
Luke
Yeah I would like to see it tested in 03 (from memory you can have over 255 characters but anything over 255 won't be visible in the cell, is that right? I no longer have the program as when you install later versions of XL earlier versions of XL get swiped. Be nice if someone could test it and get back on the forum.
Cytop from Ozgrid (who I have a ridicules amount of respect for) recently quoted the formula in another forum as the longest he had seen.
http://www.excelforum.com/the-water-cooler/944010-longest-formula-ever.html
Post 8 is the formula and you get an idea of its girth by viewing it on the Excel forum.
Begcar
I gave you a suggestion which I thought was quite reasonable. Take that formula I have gifted you and convert it to Portuguese. Oh now you want it in English...... Replace the word Portuguese with English. As I said above 15 minute find and replace job. Some kind sole might do it for you but you have the next best thing to a bespoke solution all you have to do is roll up your sleeves and do a bit of work here yourself. Post the solution and then it is 'cudos you my friend'.
Have a great day everyone
Smallman
Did you read my first post. I believe it is post 4 above!!!
That is where the initial comment came from. I made the erroneous assumption that a formula of that length would take up a lot of memory and not work very well. I was wrong - like I am on many things and if you follow the thread on Ozgrid I happily admitted to my mistake. At any rate I think it is wise to change your mind as you are shown a better way or are proven to be wrong. It won't be the last time but I will keep trying to learn from my mistakes.
Luke
Yeah I would like to see it tested in 03 (from memory you can have over 255 characters but anything over 255 won't be visible in the cell, is that right? I no longer have the program as when you install later versions of XL earlier versions of XL get swiped. Be nice if someone could test it and get back on the forum.
Cytop from Ozgrid (who I have a ridicules amount of respect for) recently quoted the formula in another forum as the longest he had seen.
http://www.excelforum.com/the-water-cooler/944010-longest-formula-ever.html
Post 8 is the formula and you get an idea of its girth by viewing it on the Excel forum.
Begcar
I gave you a suggestion which I thought was quite reasonable. Take that formula I have gifted you and convert it to Portuguese. Oh now you want it in English...... Replace the word Portuguese with English. As I said above 15 minute find and replace job. Some kind sole might do it for you but you have the next best thing to a bespoke solution all you have to do is roll up your sleeves and do a bit of work here yourself. Post the solution and then it is 'cudos you my friend'.
Have a great day everyone
Smallman
shrivallabha
Excel Ninja
Why would you want to test the formula in 2003 
No need to test, it uses IFERROR at many places.
But if you go by Excel 2003 specifications it should not be a problem as it'd support 32767 characters in formula bar and 1024 in display:
http://office.microsoft.com/en-in/excel-help/excel-specifications-and-limits-HP005199291.aspx
So if someone wants to replace:
IFERROR(fn,"") with
IF(ISERROR(fn),"",fn)
can do and see if it works in 2003. Then with that construct, it will definitely be one of the longest formulas.
No need to test, it uses IFERROR at many places.But if you go by Excel 2003 specifications it should not be a problem as it'd support 32767 characters in formula bar and 1024 in display:
http://office.microsoft.com/en-in/excel-help/excel-specifications-and-limits-HP005199291.aspx
So if someone wants to replace:
IFERROR(fn,"") with
IF(ISERROR(fn),"",fn)
can do and see if it works in 2003. Then with that construct, it will definitely be one of the longest formulas.
NARAYANK991
Excel Ninja
Hi Shrivallabha ,
The length , construction etc., of the formula are all immaterial ; what matters is whether it does the following :
For all valid inputs , it should give the correct results
For any invalid input , it should not give a valid result , correct or incorrect.
Just as in a program , what is written is always with a view to whether it is general in nature ( more general code is always preferred to very specific code , unless making it more general makes it much longer or much more difficult to understand or is not really necessary according to the specifications ) , extensible , modular , maintainable .... there are many principles of good programming practice , which not many follow all the time , but which very few will argue against.
The formula which has been posted fails on many counts , not least of which is that is does not give the correct results ; discussions about its length , cleverness , speed are all , in my opinion , irrelevant.
Narayan
The length , construction etc., of the formula are all immaterial ; what matters is whether it does the following :
For all valid inputs , it should give the correct results
For any invalid input , it should not give a valid result , correct or incorrect.
Just as in a program , what is written is always with a view to whether it is general in nature ( more general code is always preferred to very specific code , unless making it more general makes it much longer or much more difficult to understand or is not really necessary according to the specifications ) , extensible , modular , maintainable .... there are many principles of good programming practice , which not many follow all the time , but which very few will argue against.
The formula which has been posted fails on many counts , not least of which is that is does not give the correct results ; discussions about its length , cleverness , speed are all , in my opinion , irrelevant.
Narayan
shrivallabha
Excel Ninja
Narayan,
I have not tested the formula and nor do I intend to test its validity and workings at least for now. I merely stated what's there in the formula which won't work in 2003 [refer to Luke[#5] and Marcus'[#12] posts] and its length and that is all about.
I don't care about formula length[in other direction as well] which I have also stated here [3 consecutive posts]:
http://chandoo.org/forum/threads/fo...irst-block-of-numbers.10845/page-3#post-65873
Similar views [like yours] were expressed by Aladin Akyurek on MrExcel:
http://www.mrexcel.com/forum/excel-...rns-non-duplicate-items-list.html#post3588782
I agree with you on the metrics you've stated.
I have not tested the formula and nor do I intend to test its validity and workings at least for now. I merely stated what's there in the formula which won't work in 2003 [refer to Luke[#5] and Marcus'[#12] posts] and its length and that is all about.
I don't care about formula length[in other direction as well] which I have also stated here [3 consecutive posts]:
http://chandoo.org/forum/threads/fo...irst-block-of-numbers.10845/page-3#post-65873
Similar views [like yours] were expressed by Aladin Akyurek on MrExcel:
http://www.mrexcel.com/forum/excel-...rns-non-duplicate-items-list.html#post3588782
I agree with you on the metrics you've stated.