Extract string with wildcards

[thematt257]

Hi,

I'm hoping you can help. I am am trying to extract a string from a cell. The string has a variable length but contains an 'x' in the middle.

An example:

A2: '100X100 Some Name For This'
A3: 'Some Name 100x200 For This'
A4: 'Some Name For This 300X10cm'

I will like to get the following results:

B2: 100X100
B3: 100x200
B4: 300X10

Is this possible?

 

[XOR LX]

Hi,

Will there ever be any other numbers within the strings apart from those to be extracted? In your examples, no, so you could use:

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),1+MMULT(AGGREGATE({14,15},6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))/ISNUMBER(0+MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)),1),{1;-1}))

Regards

 

[thematt257]

Hi,

Will there ever be any other numbers within the strings apart from those to be extracted? In your examples, no, so you could use:

=MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&1/17)),1+MMULT(AGGREGATE({14,15},6,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2)))/ISNUMBER(0+MID(A2,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2))),1)),1),{1;-1}))

Regards

Hi XOR LX,

Thank you for that. Yes, there can be other numbers within the string (My apologies, I should have given better examples). I have also found some with '.' in the string I need.

Examples:

14X60 BLOOM ANTRASITE BORDER 2
150X150X6.5 INTA1B BEIGE BORDER

I would need 14X60 and 150X150X6.5

 

[XOR LX]

Ok.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(" "&A2," ",REPT(" ",99)),MATCH(1,N(MMULT(N(ABS({52.5,88}-CODE(MID(SUBSTITUTE(UPPER(" "&A2)," ",REPT(" ",99)),ROW(INDEX(A:A,1):INDEX(A:A,LEN(SUBSTITUTE(" "&A2," ",REPT(" ",99)))-1))+{0,1},1)))<{5,1}),{1;1})=2),0)-99,198)),"cm","")

You'll have to wrap in additional SUBSTITUTE functions to account for units of measurement other than "cm" if necessary.

Regards

 

[AlKey]

Maybe this
=TRIM(LEFT(SUBSTITUTE(IF(ISNUMBER(-LEFT(A2)),A2,TRIM(RIGHT(SUBSTITUTE(LEFT(A2,MATCH(1,INDEX(-MID(A2,ROW(A$1:A$99),1),0)))," ",REPT(" ",50)),50)))," ",REPT(" ",50)),50))

Unknown
	A	B
1
2	100X100 Some Name For This	100X100
3	Some Name 100x200 For This	100x200
4	Some Name For This 300X10cm	300X10
5	14X60 BLOOM ANTRASITE BORDER 2	14X60
6	150X150X6.5 INTA1B BEIGE BORDER	150X150X6.5
Sheet2