Formula for Detecting Currency Format

[Tiziano]

Column A of my spreadsheet has several amounts formatted in either US$,
Pound Sterling, Euro, Swedish Krona, or Swiss Franc. (I used the Format ->
Number -> Currency -> Symbol for each amount. I am actually using the
currency symbol rather than the ISO abbreviation for each currency, but I
could switch to the ISO currency abbreviation if it makes things easier...)

Now I want to create a formula in Column B that does something depending on
the type of currency in each row of Column A.
How do I detect the correct kind of currency that is in Column A so that I
can build my formula correctly? (It will be an =IF() function.)

Hopefully, this can be done without a macro as I have problems running them
due to high security settings.

Thanks.

 

[Arvi Laanemets]

Hi

There is no built-in function to retrieve currency type. Probably you can
write an UDF which does this for some list of currencies, but I'm not sure
this will be an excellent idea. Think about custom-defined currency formats
p.e.!
Another weak point in your approach is, that it will be very cumbersome to
change the currency type for some record - you have to change the format for
every cell individually, instead formatting the whole column at once.

My advice is to go with 2 columns. In column A will be amounts (general or
Numeric), and in column B you select according currency - use data
validation list for this. Now in column C for row n you can have some
formula of type
=YourFormula(An,Bn)