• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Number in words

begcar

New Member
Hi guys!
I need your help, how can I convert in excel an amount into words, but I need them in portuguese.
A1 B1
For example: $1,000 Um mil which is One Thousand in portuguese.
Is there any formula to do this?
Thanks!
 
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
 
@ Smallman & Luke Sir, sorry for Interrupting, but the formula mentioned on Ozgrid is not giving me correct result.
Just thought about sharing this with you.

Apologies if I am missing something from Ozgrid’s post.

P.S. -Len of formula = 6055
 

Attachments

  • Number.png
    Number.png
    8.6 KB · Views: 13
sorry guys, is there any way to have them as currency: for example; One thousand dolars and 10 cents.? automatically
 
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!
 
Bob

Did you read my first post. I believe it is post 4 above!!!

I remember this formula very well as I made the mistake of not testing it before I commented on it.

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

I don't think that formula would work in XL 2003, just by being too long.

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
 
Hi Sachinbizboy

Just spotted this;

@ Smallman & Luke Sir, sorry for Interrupting, but the formula mentioned on Ozgrid is not giving me correct result.

Yes it does seem like there is an error in the formula. Good opportunity while Begcar is developing it to provide some fixes :)

Take care

Smallman
 
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.
 
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
 
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.
 
Back
Top