How to convert text to decimal in openoffice calc?

Question

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???

Answer 1

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.