I need a cell that whould type EUR if the sign in the other cell is Euro sign or USD if the sign is $.
StW wrote:Hello everyone,
I need some help, please. I have a table full of data. There is a column where prices reside. These cells start either with the $ or euro sign. The cell's properties shows that the field is currency. I need a cell that whould type EUR if the sign in the other cell is Euro sign or USD if the sign is $. How can I do that?
As long as the input currency strings are text, as Villeroy points out, and the first character is always the currency sign, something like this should work:
A1: $123.45 text value!
A2: €123.46 text value!
B1: =IF(LEFT(A1;1)="$"; "USD"; IF(LEFT(A1;1)="€"; "EUR"; "?"))
menu:View>Highlight Values (Ctrl+F8)
Numbers are shown with blue font, text is in black. I assume you are struggling with literal text.
A text "$ 12.34" is not a number, so no number format applies to it.
Assuming that your locale is somewhat English with dotted decimals you can simply remove all the currency-prefixes from the text through find/replace. This should result in numeric values like 12.34 which can be formatted as you like.
Are the signs part of the data entry, or are they due to formatting? It might make a difference. I'm also curious as to why you would want to do that. Are the currency indicators not enough? I can see other possibilities, depending on the data source and the need.
|Edit: If you really want text values, as your thread title suggests, then you can use function TEXT. =TEXT(some_number;format_string) use a format_string according to the corresponding format in the cell-formatting dialog. =TEXT(A1;"[$€-462] #,##0.00;[$€-462] #,##0.00-".|
So you have numbers in the cells. The value of the cell is number 12.34 rather than "$ 12.34" or something.
Edit: I have tried the syntax provided. Where the currrency sign is € the syntax works. But, when the currency sign is $, the formula still shows €. Can you help me from here?|
Anyway, I really really appreciate the help commming from all the members. It really helps.
Users browsing this forum: Google [Bot] and 12 guests