extract from a string of text

[Antonio557580]

Greetings.
Please help with the fallowing issue.

i have this data

Surub Seko 3,0x 20 baza PZ VG
Surub Seko 3,0x 30 baza PZ VG
SURUB LEMN INE 2,5X10VRAC
SURUB LEMN INE 2,5X16VRAC
SURUB LEMN INE 2,5X20VRAC
SURUB LEMN INEC 3X10 VRAC
SURUB PAL CAP INECAT 3*16
SURUB PAL CAP INECAT 3*20
SURUB LEMN INEC 3X50 VRAC
SURUB PAL CAP INECAT 3,5*16
ANCORA OT ZN 8X80MM VRAC


i want to extract from it the number values
and the x or the * to remain there between the numbers

to return something like this

3,0x 20
3,0x 30
2,5X10
2,5X16
2,5X20
3X10
3*16
3*20
3X50
3,5*16
8X80

thank you.

 

[Luke M]

Hello, and welcome to the forum.

If you want a single formula, confirm this as an array:
=LEFT(MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($A$1:$A$100),1)),0),999),1+MAX(ROW($A$1:$A$100)*ISNUMBER(1*MID(A2,ROW($A$1:$A$100),1)))-MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($A$1:$A$100),1)),0))

Array formulas are confirmed using Ctrl+Shift+Enter, not just Enter.

Or, if you want to use a helper column, formula is a little bit easier to follow.
First array formula then is:
=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($A$1:$A$100),1)),0),999)
and final formula is:
=LEFT(B2,MAX(ROW($A$1:$A$100)*ISNUMBER(1*MID(B2,ROW($A$1:$A$100),1))))

 

[Antonio557580]

Thank you for the effort the thing is that i have a huge volume (hundreds of cells in a column) of day to day modifying cells with data similar to this cell
SUR AUTOF HEX 4,2X32 VRAC
and all i need from this cells is only the value 4,2X32
other cells come like this
SURUB PAL CAP INECAT 3*16
i need from here only 3*16
they are mixed up like this on a column
SURUB PAL CAP INECAT 3,5*50
SURUB LEMN INEC 3,5X60 KG
SURUB PAL CAP INECAT 4*12
SURUB PAL CAP INECAT 4*16
SURUB PAL CAP INECAT 4*20
SURUB PAL CAP INECAT 4*25
SURUB PAL CAP INECAT 4*30
SURUB PAL CAP INECAT 4*35
SUR.PAL CIN. 4.0X40

i do not mind using a helper column but i tried the formulas and did not work.
in A1 was my data in B1 was the first formula

=MID(A2,MATCH(TRUE,ISNUMBER(1*MID(A2,ROW($A$1:$A$100),1)),0),999)

and in C1 was the second formula

=LEFT(B2,MAX(ROW($A$1:$A$100)*ISNUMBER(1*MID(B2,ROW($A$1:$A$100),1))))

what do i do wrong? :/

 

[Aby]

Hi
Check the file below. In Helper cols. you have to press ctrl+shift+enter after entering formula given by Luke M, else you will get error.

These are called array formulas

Thanks Luke M

 

[Antonio557580]

many thanx exel ninjas!!!