Convert Text into a Formula in Calc

Question

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.

Answer 1

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.