format to calculation cell

M

mdma

I have a spreadsheet which has a weight column. This is exported as
25kg, 1000kg, 10x2kg from another program.

I am trying to use this weight column in a calculation. I have
inserted a new column and copied the information over and then used the
find/replace to remove the KG to use in the calc cell. This works fine
for the 25 / 1000 etc, but the 10x2 will not caculate.

Is there a way that when i paste the cells that i can get it to change
to 20 automatically. ( i presume that the x will need to be a * ?)

thanks.
 
C

CLR

How about just highlighting your column, then Edit > Replace > FindWhat:
10x2 > ReplaceWith: 20 > Replace all............


Vaya con Dios,
Chuck, CABGx3
 
O

olasa

After kg is removed, insert this formula in Column B:
=IF(ISNUMBER(FIND("x",A1)),LEFT(A1,FIND("x",A1)-1)*MID(A1,FIND("x",A1)+1,255),A1)

Ola Sandström


Note:
This method can also be used to remove kg
 
P

Phil Wales

Click on the cell, then
Data>Text To Column
Delimited
click on ohter and put x in the box
And Volia
 
M

mdma

thanks for your replies all very much appreciated.

with the find and replace, are others apart from 2x10, so it would be a
case of looking for each one in order to do it.

With the text to columns method if i split on the the x then the 2 and
10 would be in different columns.. this could possible work, but would
need a further calculation in the process.

I tried the first method, and this worked perfectly (after realising
the X was case sensitive!)
 
Top