format to calculation cell
 

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.


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=378934

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


Vaya con Dios,
Chuck, CABGx3


"mdma" wrote in message
...

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.


--
mdma
------------------------------------------------------------------------
mdma's Profile:
http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=378934

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


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378934

Click on the cell, then
DataText To Column
Delimited
click on ohter and put x in the box
And Volia



"mdma" wrote:


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.


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=378934

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!)


--
mdma
------------------------------------------------------------------------
mdma's Profile: http://www.excelforum.com/member.php...o&userid=24284
View this thread: http://www.excelforum.com/showthread...hreadid=378934

Thanks for the feedback.
Happy it worked
Ola Sandström


--
olasa
------------------------------------------------------------------------
olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760
View this thread: http://www.excelforum.com/showthread...hreadid=378934