Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

1 Answer 1

up vote 1 down vote accepted

Assuming you want to manipulate the data of cell A1:

=IF(ISERR(LEFT(A1;SEARCH("[^0-9.,]";A1)-1));A1;LEFT(A1;SEARCH("[^0-9.,]";A1)-1))

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 > OpenOffice.org 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

 
discard

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.