format to calculation cell

[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.

[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

[CLR]

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


Vaya con Dios,
Chuck, CABGx3


"mdma" <mdma.1qm56d_1118747104.5967@excelforum-nospam.com> wrote in message
news:mdma.1qm56d_1118747104.5967@excelforum-nospam.com...
>
> 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
>

[Phil Wales]

Click on the cell, then
Data>Text 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
>
>

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

[olasa]

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