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.

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

1 Answer 1

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

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.