TextJoin help plz

[keef2]

Hi,

Looking to extract the #'s in this text string. I would like to have 3 separate columns for the output one would be 45 then would be 5 and last would be 100 for this example. Right now my formula extracts all #'s from the text string. Curious what the best approach would be. Thanks!

Copy of CHICAGO IL 6.1.22 PRICE PAGE.xlsx
	B																R	S
11	JM TPO 45MIL 5'X100' WHITE																455100
TPO

Cell Formulas
Range	Formula
R11	R11	=TEXTJOIN("",TRUE,IFERROR((MID(B11,ROW(INDIRECT("1:"&LEN(B11))),1)*1),""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B69:D85,B130:D134,B87:D92,B94:D105,B107:D109,B125:D125,B148:D150,B11:D11,K10:P10,B118:D123,K22:Q25,K36:Q46,K27:Q33,B136:H137,B152:H157,J12:Q12,B127:K127,B66:K66,B48:K48,B9:K9,B10:I10,I67:P67,I117,I128:P128,I49:P49,I11:Q11,J69:Q85,J87:Q92,J94:Q105,J107:Q109	Cell	contains a blank value	text	NO

 

[keef2]

seems like i dont have the right approach. But this is something messing with. There has to be a better way any help is appreciated.

Copy of CHICAGO IL 6.1.22 PRICE PAGE.xlsx
	B																R	S	T	U	V	W	X
10	Product																TexJoin Extract		MIL	Width	Length	SF	LBS/SF
11	JM TPO 45MIL 5'X100' WHITE																455100		45	5	100	500	0.284
12	JM TPO 45MIL 5'X100' GREY																455100		45	5	100	500	0.284
13	JM TPO 45MIL 5'X100' TAN																455100		45	5	100	500	0.284
14	JM TPO 45MIL 6'X100' WHITE																456100		45	6	100	600	0.285
15	JM TPO 45MIL 8'X100' WHITE																458100		45	8	100	800	0.28
16	JM TPO 45MIL 8'X100' GREY																458100		45	8	100	800	0.28
17	JM TPO 45MIL 10'X100' WHITE																4510100		45	10	100	1000	0.284
18	JM TPO 45MIL 10'X100' GREY																4510100		45	10	100	1000	0.284
19	JM TPO 45MIL 10'X100' TAN																4510100		45	10	100	1000	0.284
20	JM TPO 45MIL 12'X100' WHITE																4512100		45	12	100	1200	0.28417
21	JM TPO 45 MIL 12' x 100' GREY																4512100		45	12	100	1200	0.28417
22	JM TPO 60MIL 5'X100' WHITE																605100		60	5	100	500	0.356
23	JM TPO 60MIL 5'X100' GREY																605100		60	5	100	500	0.356
24	JM TPO 60MIL 5'X100' TAN																605100		60	5	100	500	0.356
25	JM TPO 60MIL 6'X100' WHITE																606100		60	6	100	600	0.36
26	JM TPO 60MIL 6'X100' GREY																606100		60	6	100	600	0.36
27	JM TPO 60MIL 8'X100' WHITE																608100		60	8	100	800	0.3625
28	JM TPO 60MIL 8'X100' GREY																608100		60	8	100	800	0.3625
29	JM TPO 60MIL 8'X100' TAN																608100		60	8	100	800	0.3625
30	JM TPO 60MIL 10'X100' WHITE																6010100		60	10	100	1000	0.357
31	JM TPO 60MIL 10'X100' GREY																6010100		60	10	100	1000	0.357
32	JM TPO 60MIL 10'X100' TAN																6010100		60	10	100	1000	0.357
33	JM TPO 60MIL 12'X100' WHITE																6012100		60	12	100	1200	0.35833
34	JM TPO 60MIL 12'X100' GREY																6012100		60	12	100	1200	0.35833
35	JM TPO 60MIL 12'X100' TAN																6012100		60	12	100	1200	0.35833
36	JM TPO 80MIL 5'X100' WHITE																805100		80	5	100	500	0.49
37	JM TPO 80MIL 5'X75' GREY																80575		80	5	75	375	0.49067
38	JM TPO 80MIL 5'X75' TAN																80575		80	5	75	375	0.49067
39	JM TPO 80MIL 6'X75' WHITE																80675		80	6	75	450	0.49111
40	JM TPO 80MIL 8'X100' WHITE																808100		80	8	100	800	0.49125
41	JM TPO 80MIL 8'X75' GREY																80875		80	8	75	600	0.49167
42	JM TPO 80MIL 8'X75' TAN																80875		80	8	75	600	0.49167
43	JM TPO 80MIL 10'X100' WHITE																8010100		80	10	100	1000	0.491
44	JM TPO 80MIL 10'X75' GREY																801075		80	10	75	750	0.492
45	JM TPO 80MIL 10'X75' TAN																801075		80	10	75	750	0.492
46	JM TPO 80MIL 12'X75' WHITE																801275		80	12	75	900	0.49111
TPO

Cell Formulas
Range	Formula
V11:V36,V40,V43	V11	=RIGHT(R11,3)
W11:W46	W11	=U11*V11
X11:X46	X11	=E11/W11
V37:V39,V41:V42,V44:V46	V37	=RIGHT(R37,2)
U11:U43	U11	=IF(LEN(R11)>6,MID(R11,3,2),MID(R11,3,1))
R11:R46	R11	=TEXTJOIN("",TRUE,IFERROR((MID(B11,ROW(INDIRECT("1:"&LEN(B11))),1)*1),""))
T11:T46	T11	=LEFT(R11,2)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B34:B35	Cell	contains a blank value	text	NO
B26	Cell	contains a blank value	text	NO
B21	Cell	contains a blank value	text	NO
B41:C42,B17:D20,B16:C16,B12:D15,B22:D25,B27:D33,B36:D40,C38:C42,B43:D46	Cell	contains a blank value	text	NO
B69:D85,B130:D134,B87:D92,B94:D105,B107:D109,B125:D125,B148:D150,B11:D11,K10:P10,B118:D123,K22:Q25,K36:Q46,K27:Q33,B136:H137,B152:H157,J12:Q12,B127:K127,B66:K66,B48:K48,B9:K9,B10:I10,I67:P67,I117,I128:P128,I49:P49,I11:Q11,J69:Q85,J87:Q92,J94:Q105,J107:Q109	Cell	contains a blank value	text	NO

 

[Fluff]

How about

Fluff.xlsm
	B	C	D	E	F
10	Product
11	JM TPO 45MIL 5'X100' WHITE		45	5	100
12	JM TPO 45MIL 5'X100' GREY		45	5	100
13	JM TPO 45MIL 5'X100' TAN		45	5	100
14	JM TPO 45MIL 6'X100' WHITE		45	6	100
15	JM TPO 45MIL 8'X100' WHITE		45	8	100
16	JM TPO 45MIL 8'X100' GREY		45	8	100
17	JM TPO 45MIL 10'X100' WHITE		45	10	100
18	JM TPO 45MIL 10'X100' GREY		45	10	100
19	JM TPO 45MIL 10'X100' TAN		45	10	100
20	JM TPO 45MIL 12'X100' WHITE		45	12	100
21	JM TPO 45 MIL 12' x 100' GREY		45	12	100
22	JM TPO 60MIL 5'X100' WHITE		60	5	100
23	JM TPO 60MIL 5'X100' GREY		60	5	100
24	JM TPO 60MIL 5'X100' TAN		60	5	100
25	JM TPO 60MIL 6'X100' WHITE		60	6	100
26	JM TPO 60MIL 6'X100' GREY		60	6	100
27	JM TPO 60MIL 8'X100' WHITE		60	8	100
28	JM TPO 60MIL 8'X100' GREY		60	8	100
29	JM TPO 60MIL 8'X100' TAN		60	8	100
30	JM TPO 60MIL 10'X100' WHITE		60	10	100
31	JM TPO 60MIL 10'X100' GREY		60	10	100
32	JM TPO 60MIL 10'X100' TAN		60	10	100
33	JM TPO 60MIL 12'X100' WHITE		60	12	100
34	JM TPO 60MIL 12'X100' GREY		60	12	100
35	JM TPO 60MIL 12'X100' TAN		60	12	100
36	JM TPO 80MIL 5'X100' WHITE		80	5	100
37	JM TPO 80MIL 5'X75' GREY		80	5	75
38	JM TPO 80MIL 5'X75' TAN		80	5	75
39	JM TPO 80MIL 6'X75' WHITE		80	6	75
40	JM TPO 80MIL 8'X100' WHITE		80	8	100
41	JM TPO 80MIL 8'X75' GREY		80	8	75
42	JM TPO 80MIL 8'X75' TAN		80	8	75
43	JM TPO 80MIL 10'X100' WHITE		80	10	100
44	JM TPO 80MIL 10'X75' GREY		80	10	75
45	JM TPO 80MIL 10'X75' TAN		80	10	75
46	JM TPO 80MIL 12'X75' WHITE		80	12	75
Sheet3

Cell Formulas
Range	Formula
D11:F21	D11	=TOROW(TEXTSPLIT(UPPER(B11),CHAR(SEQUENCE(,26,65)),{" ","'"},1,0))
D22:F46	D22	=TOROW(TEXTSPLIT(B22,CHAR(SEQUENCE(,26,65)),{" ","'"},1))
Dynamic array formulas.

 

[keef2]

You are such a stud Fluff!!! works like a charm, Thank you from racking my brain trying to figure this out the long way...

Cheers!!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[keef2]

You're welcome & thanks for the feedback.

Fluff -- Spoke a little 2 quickly. Have 1 issue and I don't fully understand that formula yet. Here is the error i get in the second data set of many. Any thoughts?

I understand it is trying to pull all #'s so that means in the spill case it shows 4 separate #'s but i don't need the last one in this condition. (ie the 115 or 150 or 135 etc).

Copy of CHICAGO IL 6.1.22 PRICE PAGE.xlsx
	B																R	S	T	U	V
50	JM TPO 60MIL 10'X100' WHITE FB 115																#SPILL!			0
51	JM TPO 60MIL 10'X100' GREY FB 115																#SPILL!			0
52	JM TPO 60MIL 10'X100' TAN FB 115																#SPILL!			0
53	JM TPO 60MIL 10'X75' WHITE FB 150																#SPILL!			0
54	JM TPO 80MIL 10'X50' WHITE FB 135																#SPILL!			0
55	JM TPO 80MIL 10'X50' GREY FB 135																#SPILL!			0
56	JM TPO 80MIL 10'X50' WHITE FB 175																#SPILL!			0
57	JM TPO 45 MIL 5' X 100' WHITE MIN																45	5	100	500	0.286
58	JM TPO 45 MIL 8' X 100' WHITE MIN																45	8	100	800	0.275
59	JM TPO 45 MIL 10' X 100' WHITE MIN																45	10	100	1000	0.275
60	JM TPO 60 MIL 5'X100' WHITE MIN																60	5	100	500	0.366
61	JM TPO 60 MIL 10'X100' WHITE MIN																60	10	100	1000	0.395
62	JM TPO 60 MIL 12' X 100' WHITE MIN																60	12	100	1200	0.35333
63	JM TPO SA 60MIL 10'X100' WHITE																60	10	100	1000	0.396
64	JM TPO SA FLASHING MEMBRANE 60MIL 5'X100' WHITE																60	5	100	500	0.396
TPO

Cell Formulas
Range	Formula
U50:U64	U50	=IFERROR(S50*T50,"")
V50:V64	V50	=IFERROR(E50/U50,"")
R50:R56,R57:T64	R50	=TOROW(TEXTSPLIT(UPPER(B50),CHAR(SEQUENCE(,26,65)),{" ","'"},1,0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B69:D85,B130:D134,B87:D92,B94:D105,B107:D109,B125:D125,B148:D150,B11:D11,K10:P10,B118:D123,K22:Q25,K36:Q46,K27:Q33,B136:H137,B152:H157,J12:Q12,B127:K127,B66:K66,B48:K48,B9:K9,B10:I10,I67:P67,I117,I128:P128,I49:P49,I11:Q11,J69:Q85,J87:Q92,J94:Q105,J107:Q109	Cell	contains a blank value	text	NO

 

[Fluff]

You get that error as there are 4 sets of numbers in those cells, not 3. Do you just want the 1st 3?

 

[keef2]

You get that error as there are 4 sets of numbers in those cells, not 3. Do you just want the 1st 3?

Yea that would be great what do i need to change for 1st 3? Sorry didnt notice that the send data set had that variable.

 

[Fluff]

How about

=TAKE(TOROW(TEXTSPLIT(UPPER(B50),CHAR(SEQUENCE(,26,65)),{" ","'"},1,0)),,3)

 

[keef2]

Perfection, thanks again!