Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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.

share|improve this answer
    
+1 for VALUE. For simpler cases, that alone is enough. – matt Mar 13 at 20:17

You must log in to answer this question.

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