1

I have a column which contains:

€ 53,28
€ 1.336,0
€ 69,90
€ 296,50
€ 899,00
€ 149,90
€ 697,40

and so on. I want to sum the column up, but for openoffice it is text and not a decimal.

What I did:

=SUBSTITUTE(F7,".","") to get rid of the the dot
|€ 1336,0|
=SUBSTITUTE(G7,"€","") to get rid of the euro symbol
| 1336,0|
=RIGHT(H7, LEN(H7)-1) to get rid of the blank
|1336,0|
=SUBSTITUTE(I7,",",".") to change the comma to dots
|1336.0|

and after that I copy the whole column and paste it as "unformatted text" only after these steps I get a number with which openoffice can work.

Isn't there an easier way???

1 Answer 1

0

First, you can combine all your formulas into one:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";".")

Then add the VALUE formula to the mix (Returns a number, given a text representation):

=VALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;"€ ";"");".";"");",";"."))

Calc should now recognize the resultant cells of this formula as numbers.

2
  • +1 for VALUE. For simpler cases, that alone is enough.
    – matanox
    Mar 13, 2016 at 20:17
  • This formula incorrectly parses '1,091.00 into 1.09 (well I can see that was the original OP's request, but just giving hands up to people who have a bit different use case)
    – Dimitry K
    May 16, 2017 at 9:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .