Apache OpenOffice Community Forum

Hi,
I have this problem with the dollar or euro symbol, everytime I multiply it with another column I get "#value", it just doesn't recognise the number with a currency symbol before it, the amount always appears in the left hand side of the box, it should be on the right, it will ownly work when I take the currency symbol away and retype in the amount, this is annoying when your dealing with a lot of numbers.

Francis

Format the cells to proper currency format then enter the values without the symbols.

HTH

Hi, and welcome to the forum.

The #VALUE! that you're getting means that the cells contain text instead of numbers. Use View, Value Highlighting to verify this. Numbers will be in blue. If the cells containing the currency symbols aren't blue it verifies that they are text. Before you can use a cell in a multiplication operation it must contain a number. Highlight (select) the cells containing these values and use Format, Cells, Numbers, and set the Category to Currency. Before you do OK, you can select the desired currency option in the Format field (like USD $ or EUR € Spanish). This changes the cell formatting so that numbers entered in them will be displayed as currency.

However Format Cells doesn't change any values that are already in the reformatted cells. Since you presumably don't want to re-enter all the values manually, select (highlight) these cells and we'll let Edit, Find & Replace do that automatically. If the cells now contain a dollar sign, use Search for $ and leave the Replace with field empty. Replace All will remove all the dollar signs and re-enter the numbers. Since the cells are now formatted as currency, they will display a dollar sign. However, if you select one of these cells and look in the formula bar, there is no $, just a number. View, Value Highlighting will display these values in blue. Then your multiplication formula will work.

Without knowing where these text values came from, it's not possible to explain why they weren't initially recognized as numbers. I've seen cases where data was pasted into Calc from a web page and currency values are interpreted as text. It may be possible to get text from the "Import" dialog that's used when Calc reads files formatted in CSV format. There are probably other ways for this difficulty to occur. If you create a new spreadsheet and type $1.00 in a cell, Calc will recognize that this is a number and that you want Currency format for the cell, so apparently your values were entered another way. Another variable is the "locale", which affects how Calc interprets what is entered. For example in the United States locale, the digit grouping character is a comma and the separator between the integer and fractional parts of a number is the period, so 1,234 is interpreted as one thousand two hundred thirty four. But in other locales, those roles are reversed and 1,234 is interpreted as one and 234 thousandths. Perhaps your locale didn't allow Calc to interpret the currency data as numbers. But no matter what happened, you can fix it by using Format, Cells, Numbers, Currency, then Edit, Find & Replace.

If this answered your question please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

Hi Mr Programmer,

Thanks very much, I got it to work, I was a week trying to figure this out.

Regards Frank

The € and the $ symbols work as expected in their respective locale environment (application locale or cell's number format locale).
12€ entered in my German default context into a cell with the unspecific number format "Standard" applies a currency format on the fly. I get a numeric cell value of 12 displayed as "12,00 €"
Same with input:
12,789€ =>12,79 €
12,789 € =>12,79 €
€12,789 =>12,79 €
€ 12,789 =>12,79 €

Switching to USAmerican context where the same unspecific number format is named "General":
$12,789 => $12,789.00 [comma is thousands separator]
$12.789 => $12.79
$ 12.789 => $12.79
12.789$ => $12.79
12.789 $ => $12.79

Apart from the cell showing "$12,789.00" all the cells have the same numeric value 12.789. This is the most important fact of all. The cell value is just that decimal number 12.789. The formatting has no impact on any calculation. The most important issue is that you get the right numbers into the right cells. You can format the right values to anything you like. Even if the results may LOOK quite different after you formatted all your figures to, say dates, the numeric values are exactly the same. When you enter arbitrary currency signs into a wrong context you do not get any number at all. The cell gets a text value as if you had typed a word into it.

f1donnelly wrote:Thanks very much, I got it to work, I was a week trying to figure this out.

If you try to learn anything by try and error you won't get anywhere. A tiny little bit of basic knowlege together with some systematic testing lets you do some giant steps within a few hours.

I followed these instructions and the 'Replace' did not remove my $ sign.
Solution
I found that under 'Other Options' in the Find and Replace dialogue, that 'Regular expressions' was checked TRUE. After unchecking 'Regular expressions' 'Replace' successfully removed the $ signs.