Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

In Open Office Spreadsheet I have cells with strings like "123kg" "3,45m" "0,5g" "345apples" etc.

Which is the function that can parse the numeric prefix on these numbers? So I get "123" "3,45" "0,5" "345".

The VALUE function expects a 100% correct numeric value without postfixes. It cannot parse the prefix only.

share|improve this question
up vote 1 down vote accepted

Assuming you want to manipulate the data of cell A1:


You might want to keep only . or , as a separator in the (two) regexps.

What it does is take the first x characters of your cell, where x is the first non-numeric (or non decimal separator) character. The IF(ISERR(...)) ensures you still got a number in case you don't have alpha characters at all. You can remove it if all our values have a unit.

You have to have the option checked in "Tools > Options > Calc > Calculate > Enable regular expressions in formulas".

share|improve this answer
Quite hackish but works. I thought there is a standard function to do that. Thanks. – Calmarius Aug 26 '12 at 17:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.