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

 

[kvsrinivasamurthy]

In R11 then copied down

=LET(a,IF(ISNUMBER(1*MID(B11,ROW(INDIRECT("$1:$"&LEN(B11))),1)),MID(B11,ROW(INDIRECT("$1:$"&LEN(B11))),1)," "),b,TEXTJOIN("",FALSE,a),c,TRIM(b),TEXTSPLIT(c," "))

 

[ExceLoki]

@Fluff 's solution is much cleaner and shorter than mine, but i was looking for another way to solve this and came up with this
---------------------------------

Book1
	B																R	S	T	U	V
1	Product																MIL	Width	Length	SF	LBS/SF
2	JM TPO 45MIL 5'X100' WHITE																45	5	100	500	0
3	JM TPO 45MIL 5'X100' GREY																45	5	100	500	0
4	JM TPO 45MIL 5'X100' TAN																45	5	100	500	0
5	JM TPO 45MIL 6'X100' WHITE																45	6	100	600	0
6	JM TPO 45MIL 8'X100' WHITE																45	8	100	800	0
7	JM TPO 45MIL 8'X100' GREY																45	8	100	800	0
8	JM TPO 45MIL 10'X100' WHITE																45	10	100	1000	0
9	JM TPO 45MIL 10'X100' GREY																45	10	100	1000	0
10	JM TPO 45MIL 10'X100' TAN																45	10	100	1000	0
11	JM TPO 45MIL 12'X100' WHITE																45	12	100	1200	0
12	JM TPO 45 MIL 12' x 100' GREY																45	12	100	1200	0
13	JM TPO 60MIL 5'X100' WHITE																60	5	100	500	0
14	JM TPO 60MIL 5'X100' GREY																60	5	100	500	0
15	JM TPO 60MIL 5'X100' TAN																60	5	100	500	0
16	JM TPO 60MIL 6'X100' WHITE																60	6	100	600	0
17	JM TPO 60MIL 6'X100' GREY																60	6	100	600	0
18	JM TPO 60MIL 8'X100' WHITE																60	8	100	800	0
19	JM TPO 60MIL 8'X100' GREY																60	8	100	800	0
20	JM TPO 60MIL 8'X100' TAN																60	8	100	800	0
21	JM TPO 60MIL 10'X100' WHITE																60	10	100	1000	0
22	JM TPO 60MIL 10'X100' GREY																60	10	100	1000	0
23	JM TPO 60MIL 10'X100' TAN																60	10	100	1000	0
24	JM TPO 60MIL 12'X100' WHITE																60	12	100	1200	0
25	JM TPO 60MIL 12'X100' GREY																60	12	100	1200	0
26	JM TPO 60MIL 12'X100' TAN																60	12	100	1200	0
27	JM TPO 80MIL 5'X100' WHITE																80	5	100	500	0
28	JM TPO 80MIL 5'X75' GREY																80	5	75	375	0
29	JM TPO 80MIL 5'X75' TAN																80	5	75	375	0
30	JM TPO 80MIL 6'X75' WHITE																80	6	75	450	0
31	JM TPO 80MIL 8'X100' WHITE																80	8	100	800	0
32	JM TPO 80MIL 8'X75' GREY																80	8	75	600	0
33	JM TPO 80MIL 8'X75' TAN																80	8	75	600	0
34	JM TPO 80MIL 10'X100' WHITE																80	10	100	1000	0
35	JM TPO 80MIL 10'X75' GREY																80	10	75	750	0
36	JM TPO 80MIL 10'X75' TAN																80	10	75	750	0
37	JM TPO 80MIL 12'X75' WHITE																80	12	75	900	0
Sheet2

Cell Formulas
Range	Formula
R2:R37	R2	=LET(prod,SUBSTITUTE(B2," ",""),MID(prod,SEARCH("MIL",prod)-((SEARCH("MIL",prod))-(SEARCH("TPO",prod)+3)),((SEARCH("MIL",prod))-(SEARCH("TPO",prod)+3))))
S2:S37	S2	=LET(prod,SUBSTITUTE(B2," ",""),MID(prod,SEARCH("'X",prod)-((SEARCH("'X",prod)-1)-(SEARCH("MIL",prod)+2)),(SEARCH("'X",prod)-1)-(SEARCH("MIL",prod)+2)))
T2:T37	T2	=SUBSTITUTE(LET(prod,SUBSTITUTE(B2," ",""),MID(prod,SEARCH("'X",prod)+2,3)),"'","")
U2:U37	U2	=S2*T2
V2:V37	V2	=E2/U2

 

[keef2]

Thanks Guys. All solutions work but Fluff & kvsrinivasamurthy methods seem to be cleanest without many modifications for other variations. Appreciate everyone's time on this. Big help for me thanks again!

 

[Fluff]

Glad we could help & thanks for the feedback.