want to split data in one cell

[learning_grexcel]

Hi,

I have a column with this kind of data :


60PCS X 9.3OZ
54PCS X 8.5OZ
1PCS X 7.25INCH
1PCS X 15KG
1PKT X 1KG
1PCS X 1KG
1PCS X 1KG
1PCS X 1KG

Now I want the first number 60 in one column and the second number 9.30 in another column. Similarly whole column. Could you please advise me how I can do it?

 

[learning_grexcel]

You are correct. Thanks. I will try this now.

 

[pgc01]

I'd just add 1 to the offset after the search:

=-LOOKUP(0,-MID(A2,SEARCH(" x ",A2)+3,{1,2,3,4,5,6}))

 

[learning_grexcel]

Thanks to all
I have come across one problem using the formula pgc01.
In some cells it is written as just "KG". In that case I want the result in both the column to be "1".
And similarly in some place it is just "pc x 15kg". In this case I want in first column it should be "1" and in next "15". Is it possible?