Currency format

[Guest]

Hello,
Please help me.
In a spreadsheet I need to assign the currency that we receive money in.
There is a column that states the currency in a 3 letter code.
The amounts are downloaded from a CSV file.
Here is the fomula that I am using.
=IF(ISTEXT($A51),IF($E51="USD",TEXT($G51,"$
#,##0.00_)"),IF($E51="EUR",TEXT($G51,"€ #,##0.00_)"),"")))

The problem with this formula is that it converts the amount to text.
How can I get this or is there another formula that will give me the answer
in the relevant currency, as a value with the relevant currency, without
having to set the format using the "Cell Format" function?


Thank you for your help.

Max

 

[Guest]

There are many ways using Text or Value functions but I prefer the easiest
one as follows:
Any simple arithmetic calculation on text will convert them to numbers so
add a zero to the formula (+0) and it will turn to number.

 

[Guest]

What about the currency format?

There are many ways using Text or Value functions but I prefer the easiest
one as follows:
Any simple arithmetic calculation on text will convert them to numbers so
add a zero to the formula (+0) and it will turn to number.

 

[Ragdyer]

Using your present formula, say in Column H, is displaying the correct
format, according to the contents of Column E.

You can add these "text" values, as long as you *don't* use Sum().

For example:

=H51+H52+H53+H54 ... etc.

And, of course, to format *that* totaling cell, for example:

=TEXT($H51+$H52+$H53+$H54+$H55,"$ #,##0.00_)")

 

[Ragdyer]

Checking back, I don't think that my suggestion will work for adding those
"text" cells, unless the regional settings recognize the currency symbol.
Checking with USD ($) works for me.
The only way I could get the € to total, was to change my regionals.

Sorry!

 

[Guest]

Hello Ragdryer,
Thank you for your effort.
If you try the Function "Fixed" under the Text formula selection it will
give you the Currency selection that your computer is set at.

I guess its back to the drawing board for me.

Thank you again.

Max

 

[Vasant Nanavati]

If you can use a macro solution it would be pretty easy, I think.
_________________________________________________________________________

 

[Guest]

Hello Vasant,
Thank you for you effort, I am sure using a macro will be the only way.

Thank you and best regards

Max

 

[Guest]

Hello Ragdryer,
I made a mistake the function is not "Fixed"it is "Dollar".

Thanks

Max