How can I can I extract this?

[Romano_odK]

Good morning,

I got about 6000 items with sizes in them. In this case I need to extract those sizes to another column. So In case of the first line I need to get '6x2 mm' into a separate column. Is there a way to do this?

Thank you for your time and have a great day.

Artikelen beheren XML 4.02 (beta).xlsm
	C
8	Vito Glaserfix 111 6x2 mm wit - 10x25 m
9	Vito Glaserfix 111 6x2 mm zwart - 10x25 m
10	Vito Glaserfix 111 6x3 mm wit - 10x25 m
11	Vito Glaserfix 111 6x3 mm zwart - 10x25 m
12	Vito Glaserfix 111 6x4 mm wit - 10x25 m
13	Vito Glaserfix 111 6x4 mm zwart - 10x25 m
14	Vito Glaserfix 111 9x2 mm wit - 10x25 m
15	Vito Glaserfix 111 9x2 mm zwart - 10x25 m
16	Vito Glaserfix 111 9x3 mm wit - 10x25 m
17	Vito Glaserfix 111 9x3 mm zwart - 10x25 m
18	Vito Glaserfix 111 9x4 mm wit - 10x25 m
19	Vito Glaserfix 111 9x4 mm zwart - 10x25 m
20	Vito Glaserfix 111 9x5 mm wit - 10x10 m
21	Vito Glaserfix 111 9x5 mm zwart - 10x10 m
22	Vito Glaserfix 111 9x6 mm wit - 10x10 m
23	Vito Glaserfix 111 9x6 mm zwart - 10x10 m
Items

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C8:C10119	Expression	=LEN(C8)>60	text	NO

 

[Georgiboy]

One way:

Book1
	A	B
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6
Sheet1

Cell Formulas
Range	Formula
B1:B16	B1	=LET( ts,TEXTSPLIT(A1," "), TAKE(FILTER(ts,ISNUMBER(--TEXTBEFORE(ts,"x"))),,1) )

 

[Romano_odK]

One way:

Book1
	A	B
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6
Sheet1

Cell Formulas
Range	Formula
B1:B16	B1	=LET( ts,TEXTSPLIT(A1," "), TAKE(FILTER(ts,ISNUMBER(--TEXTBEFORE(ts,"x"))),,1) )

This is awesome, thank you it works.

One way:

Book1
	A	B
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6
Sheet1

Cell Formulas
Range	Formula
B1:B16	B1	=LET( ts,TEXTSPLIT(A1," "), TAKE(FILTER(ts,ISNUMBER(--TEXTBEFORE(ts,"x"))),,1) )

Thank you this solved my problem. Have a nice day.

 

[Dave Patton]

consider

TextBefore.xlsm
	A	B
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6
17
3f

Cell Formulas
Range	Formula
B1:B16	B1	=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")

 

[Dave Patton]

TextBefore.xlsm
	A	B	C		E
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2	6x2		6x2 mm
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2	6x2		6x2 mm
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3	6x3		6x3 mm
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3	6x3		6x3 mm
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4	6x4		6x4 mm
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4	6x4		6x4 mm
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2	9x2		9x2 mm
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2	9x2		9x2 mm
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3	9x3		9x3 mm
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3	9x3		9x3 mm
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4	9x4		9x4 mm
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4	9x4		9x4 mm
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5	9x5		9x5 mm
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5	9x5		9x5 mm
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6	9x6		9x6 mm
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6	9x6		9x6 mm
17
3f

Cell Formulas
Range	Formula
B1:B16	B1	=MID(A1,FIND(" ",A1,17)+1,3)
C1:C16	C1	=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")
E1:E16	E1	=TEXTBEFORE(TEXTAFTER(A1," ",3)," ",2)

 

[Scott Huish]

consider

TextBefore.xlsm
	A	B
1	Vito Glaserfix 111 6x2 mm wit - 10x25 m	6x2
2	Vito Glaserfix 111 6x2 mm zwart - 10x25 m	6x2
3	Vito Glaserfix 111 6x3 mm wit - 10x25 m	6x3
4	Vito Glaserfix 111 6x3 mm zwart - 10x25 m	6x3
5	Vito Glaserfix 111 6x4 mm wit - 10x25 m	6x4
6	Vito Glaserfix 111 6x4 mm zwart - 10x25 m	6x4
7	Vito Glaserfix 111 9x2 mm wit - 10x25 m	9x2
8	Vito Glaserfix 111 9x2 mm zwart - 10x25 m	9x2
9	Vito Glaserfix 111 9x3 mm wit - 10x25 m	9x3
10	Vito Glaserfix 111 9x3 mm zwart - 10x25 m	9x3
11	Vito Glaserfix 111 9x4 mm wit - 10x25 m	9x4
12	Vito Glaserfix 111 9x4 mm zwart - 10x25 m	9x4
13	Vito Glaserfix 111 9x5 mm wit - 10x10 m	9x5
14	Vito Glaserfix 111 9x5 mm zwart - 10x10 m	9x5
15	Vito Glaserfix 111 9x6 mm wit - 10x10 m	9x6
16	Vito Glaserfix 111 9x6 mm zwart - 10x10 m	9x6
17
3f

Cell Formulas
Range	Formula
B1:B16	B1	=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")

That woud only work if the first measurement was always the 4th piece in.

 

[Dave Patton]

Re post #6
Yes, that is correct.

The formula TEXTBEFORE(TEXTAFTER(A1," ",3)," ",2) does provide a solution to the question with the data provided.
"I need to get '6x2 mm' "