Convert currency to text

Discuss the spreadsheet application

Convert currency to text

Postby StW » Tue Jul 01, 2008 6:01 pm

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? I work on openoffice 2.3

Thank you all!!
OOo 2.3.X on Ms Windows XP
StW
 
Posts: 3
Joined: Tue Jul 01, 2008 5:03 pm

Re: convert currency to text

Postby Villeroy » Tue Jul 01, 2008 6:11 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: convert currency to text

Postby acknak » Tue Jul 01, 2008 7:09 pm

I need a cell that whould type EUR if the sign in the other cell is Euro sign or USD if the sign is $.

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"; "?"))
B1: ...
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17389
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: convert currency to text

Postby Dave » Tue Jul 01, 2008 8:59 pm

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?


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.

David.
Dave
 
Posts: 1011
Joined: Sun Dec 23, 2007 6:53 pm

Re: convert currency to text

Postby StW » Wed Jul 02, 2008 10:01 am

Thank you all for your replies. They helped me a lot, but still, I'm stuck.

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"; "?"))
B1: ...


This this worked, but only if those cells where text-type. But mine are numbers, currrency type.

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.


This thing also works, but all the values from those cells are blue. So, no text.

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.

David.


I reside in a country where we have local currency. So, I want to make a database ( imported from the file where I have all the prices). Now, I wanted a field called "curr_type" -> EUR or USD, and to be able to make conditions based on this field, because $254 and €254, converted to our local currency, differs a lot.

Can you give me a hand?

Thank you alll
StW
 
Posts: 3
Joined: Tue Jul 01, 2008 5:03 pm

Re: convert currency to text

Postby acknak » Wed Jul 02, 2008 4:36 pm

Calc treats currency values specially, but I don't really understand all the nuances.

As far as I can tell, if all your currency values are numeric, then they must have different number formats that display the two different currency symbols. If that is the case, the symbols are not part of the value and cannot be tested by any formula (that I know of, at least). You would have to copy the values, as formatted strings, to another column, then test what currency symbol is present and convert that to "USD" or "EUR".
AOO 4 • Linux • Fedora 17
User avatar
acknak
Moderator
 
Posts: 17389
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: convert currency to text

Postby Villeroy » Wed Jul 02, 2008 6:15 pm

So you have numbers in the cells. The value of the cell is number 12.34 rather than "$ 12.34" or something.
Select the cells.
menu:Format>Cells [Ctrl+1]
Tab: Numbers
Language: English(US) [this affects some defaults, mainly the dot as decimal separator]
Category: Currency
Format: use one of the predefined or add your own number format code.
Add additional formattings such as colors, borders if you like.
[OK]
Now I would call the stylist (F11) and drag the highlighted, formatted cells into the stylist. This will prompt for a name and assign the named style to the selected range. You can recall the same formatting by name by a simple double-click on the name. If you want to change something, change the style to change all the cells sharing that style.

 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-". 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: convert currency to text

Postby StW » Thu Jul 03, 2008 9:09 am

So you have numbers in the cells. The value of the cell is number 12.34 rather than "$ 12.34" or something.


It's the opposite. I have the currency symbol in front of the number, and the whole cell''s properties shows that the celll is of type currency.

I'm trying the syntax that you provided in the later edit.

Thank you very much!

 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.

Thank you! 
OOo 2.3.X on Ms Windows XP
StW
 
Posts: 3
Joined: Tue Jul 01, 2008 5:03 pm

Re: convert currency to text

Postby Villeroy » Thu Jul 03, 2008 11:30 am

Change the number format locale to exchange Euros with dollars.
There is no distinct data type for currency. In Calc each value is a floating point number unless it is a text or error. A currency symbol is added for dislplay. It is *not* part of a numeric value. A number can be formatted to represent just a plain decimal, a percent value, a currency, a time (fraction of days since a day zero 1899-12-30) or boolean (FALSE is a formatted zero TRUE is a formatted non-zero).
Try formatting your currency numbers as "YYYY-MM-DD HH:MM:SS". I promise that not a single value will be changed. You can simply change the format to anything else without changing the underlying values.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 12.04, Apache OpenOffice 4.x
User avatar
Villeroy
Volunteer
 
Posts: 17249
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Exabot [Bot] and 27 guests