How to convert text to decimal in openoffice calc?

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

wurlog

Posted 2010-12-29T13:08:13.557

Reputation: 158

Answers

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.

Siim K

Posted 2010-12-29T13:08:13.557

Reputation: 6 776

+1 for VALUE. For simpler cases, that alone is enough. – matt – 2016-03-13T20:17:49.350

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 – 2017-05-16T09:27:58.897