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:

=REPLACE(G4,FIND("oz/",G4),3,"*")

which yields:

=1.48*12

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

="="&REPLACE(G4,FIND("oz/",G4),3,"*")

but it just yields a text string.

Thanks.

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

=FIND("oz/",G4)

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

=VALUE(LEFT(G4,I4-1)) / VALUE(RIGHT(G4,LEN(G4)-I4-2))

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 .