Extracting data from a description

[BigGee]

Hello,
I am trying to extract 2 parts from a text field into separate columns. I have been trying to use the MID function and even though I have had it work in a previous similar instance I am having difficulty I think because of the fact that my model can have a different number of characters
Could someone assist please?

 

[Cubist]

Not sure what you want with the trailing words like "CLEAR", but try:

Book1
	A	B	C	D	E
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED-XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE-M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN-L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW-S
6	FAIR-11 5X5 BLUE-S CLEAR	FAIR-11	5X5	BLUE-S	CLEAR
7	PRSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PRSENT-5 SPECIAL	20X12	YELLOW-S	CLEAR
Sheet5

Cell Formulas
Range	Formula
B6:E7,B2:D5	B2	=LET(tb,TEXTBEFORE(LEFT(A2,SEARCH("?X?",A2))," ",-1),tx,TEXTSPLIT(TEXTAFTER(A2,tb)," ",,1),HSTACK(tb,tx))
Dynamic array formulas.

 

[Cubist]

Some adjustments to formula. It wouldn't have worked if you had X in the model part.

Book1
	A	B	C	D	E
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED-XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE-M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN-L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW-S
6	FAIR-11 5X5 BLUE-S CLEAR	FAIR-11	5X5	BLUE-S	CLEAR
7	PRSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PRSENT-5 SPECIAL	20X12	YELLOW-S	CLEAR
8	PXSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PXSENT-5 SPECIAL	20X12	YELLOW-S	CLEAR
Sheet5

Cell Formulas
Range	Formula
B6:E8,B2:D5	B2	=LET(x,SEARCH({" ?X? "," ??X?? "},A2),tb,TEXTBEFORE(LEFT(A2,MAX(IFERROR(x,0)))," ",-1),tx,TEXTSPLIT(TEXTAFTER(A2,tb)," ",,1),HSTACK(tb,tx))
Dynamic array formulas.

 

[BigGee]

Hi Joe,

I have checked back through the last 3 years of data and the last line reflects the most complex string that needs to be split and the 5 lines represent all of the variables.

 

[BigGee]

Some adjustments to formula. It wouldn't have worked if you had X in the model part.

Book1
	A	B	C	D	E
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED-XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE-M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN-L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW-S
6	FAIR-11 5X5 BLUE-S CLEAR	FAIR-11	5X5	BLUE-S	CLEAR
7	PRSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PRSENT-5 SPECIAL	20X12	YELLOW-S	CLEAR
8	PXSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PXSENT-5 SPECIAL	20X12	YELLOW-S	CLEAR
Sheet5

Cell Formulas
Range	Formula
B6:E8,B2:D5	B2	=LET(x,SEARCH({" ?X? "," ??X?? "},A2),tb,TEXTBEFORE(LEFT(A2,MAX(IFERROR(x,0)))," ",-1),tx,TEXTSPLIT(TEXTAFTER(A2,tb)," ",,1),HSTACK(tb,tx))
Dynamic array formulas.

Works perfectly, just wish understood it. Thank you!

 

[Cubist]

Works perfectly, just wish understood it. Thank you!

You're welcome. You can split the color and the size with another small change.

Book1
	A	B	C	D	E	F
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED	XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE	M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN	L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW	S
6	FAIR-11 5X5 BLUE-S CLEAR	FAIR-11	5X5	BLUE	S	CLEAR
7	PRSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PRSENT-5 SPECIAL	20X12	YELLOW	S	CLEAR
8	PXSENT-5 SPECIAL 20X12 YELLOW-S CLEXR	PXSENT-5 SPECIAL	20X12	YELLOW	S	CLEXR
Sheet5

Cell Formulas
Range	Formula
B6:F8,B2:E5	B2	=LET(x,SEARCH({" ?X? "," ??X?? "},A2), tb,TEXTBEFORE(LEFT(A2,MAX(IFERROR(x,0)))," ",-1), tx,TEXTSPLIT(TEXTAFTER(A2,tb),{" ","-"},,1), HSTACK(tb,tx))
Dynamic array formulas.

 

[BigGee]

Hi Cubist,

I know I am an absolute pain, and this would be addressed by Joe's previous comment but I have found an issue where the formula breaks when the measurement eg15X15 is not a full number as shown in the example below.
Is there a solution to this?

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F
1
2	JUB-1 15X15 RED-XL	JUB-1	15X15	RED	XL
3	SPECS-5 10X15 BLUE-M	SPECS-5	10X15	BLUE	M
4	CARNIVIAL-10 15X15 GREEN-L	CARNIVIAL-10	15X15	GREEN	L
5	PRSENT-5 SPECIAL 20X12 YELLOW-S	PRSENT-5 SPECIAL	20X12	YELLOW	S
6	FAIR-11 5X5 BLUE-S CLEAR	FAIR-11	5X5	BLUE	S	CLEAR
7	PRSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PRSENT-5 SPECIAL	20X12	YELLOW	S	CLEAR
8	PXSENT-5 SPECIAL 20X12 YELLOW-S CLEAR	PXSENT-5 SPECIAL	20X12	YELLOW	S	CLEAR
9	GALLERY-6 5X5'6 ORANGE-XS SUPERDUPPER_X	GALLERY-6	5X5'6	ORANGE	XS	SUPERDUPPER_X
Sheet4

Cell Formulas
Range	Formula
B6:F9,B2:E5	B2	=LET(t,TEXTSPLIT(A2,," "),x,FIND("X",t), ta,TEXTJOIN(" ",,TAKE(t,XMATCH(TRUE,MAP(t,x,LAMBDA(a,b,ISNUMBER(--CONCAT(MID(a,b+{-1,1},1))))))-1)), HSTACK(ta,TEXTSPLIT(TEXTAFTER(A2,ta),{" ","-"},,1)))
Dynamic array formulas.

 

[smozgur]

Works perfectly, just wish understood it. Thank you!

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.