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 bunch of cells in a spreadsheet like the following: 1.48oz/12

I am trying to replace the oz/ with * and then calculate it.

So far I have:


which yields:


but I don't know how to make it calculate. I tried:


but it just yields a text string.


share|improve this question
up vote 0 down vote accepted

In Excel, the answer is to use VALUE().  It’s messy.  You can do it in one shot as:

=VALUE(LEFT(G4,FIND("oz/",G4)-1)) / VALUE(RIGHT(G4,LEN(G4)-FIND("oz/",G4)-2))

If you’re willing to tie up a column (which you can hide after you’ve finished debugging) with an intermediate value, you can define, say, I4 as


... and then define your ratio (computation result) as


I’m not familiar with libreoffice-calc, so I don’t whether VALUE() is available there.

share|improve this answer
That works great, thanks. I tried a lot of different things, I didn't find that. – user77325 Oct 18 '12 at 10:47

You must log in to answer this question.

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