[Solved] Help removing a single character from many cells

[Dbrabojr]

Hi,

I have downloaded a work report but have a problem with it. All of the currency fields have a € in front of them making them impossible to sum. This occurs in column D and Q and there are 539 rows. This is the first of 6 similar reports that I need to sum. Is there a way to either remove the € symbol from all of the cells, or get calc to ignore the character and still process the SUM() formula?

Thank you for any help that you can offer as this is very frustrating.

David

[acknak]

Select the column

Edit > Find & Replace
Search for: ^[^-+\d]+
Replace with: empty
Options/Regular expressions: ON
Options/Current selection only: ON

Click "Replace All"

[Dbrabojr]

Such a simple solution that I never thought of.

Thank you.

[acknak]

(by PM)
where did ^[^-+\d]+ come from to represent the English Pound symbol in your find and replace suggestion?

The pattern is more general--it matches more than just the pound symbol.

I suggested this because
a) it's easier to type: entering a non-ascii symbol can be an issue; and
b) there may be other characters there causing Calc not to recognize the text as a number, space character(s) for example. Since we don't have a sample of the actual data, we can't be sure there's no problem other than the euro symbol.

Since any actual number value will start with a digit, or possibly a plus/minus sign, we want to match anything that can't be part of the number, which includes any currency symbol, spaces, or whatever.

So the pattern matches:
^ at the beginning of the text
[..] any single character from this set
+ one or more times

And the set of characters is:
[
^NOT
-+\d minus, plus, or any digit
]

There are quite a few regular expression resources on the web, or as software, that can help generate, explain and test such patterns.

[Lupp]

... All of the currency fields have a € in front of them making them impossible to sum. ...

It shouldn't be completely impossible.

Given that you want to add the amounts without "killing" the currency symbol then ...
1) Let's assume the amounts being formatted in a way not causing delimiter problems.
2) Let's further assume your system does "accept" patterns starting with "€" as currency amounts then:

Simply use {=SUM(VALUE(RangeOfAmounts))} entered as an "array expression".

Without assumption 2) but assumed 3) The currency symbol is always the ONE leftmost character:

Use {=SUM(VALUE(MID(RangeOfAmounts;2;999))} entered as an "array expression".

Or generally: A bit of treatment by text functions inside of "array expressions" may do the trick.