Splitting out Data

[Iain Munro]

Morning

I have data in the following format

1 8 1452122 PNNPAAADBEDLAKST Pipe, ASME B36.10M, Type E, BE, ASTM A53 Gr. B, 1.6' Sch. STD 2 8X6 1462879 BNRCABXXBEAGALST Conc. Red, ASME B16.9, BE, ASTM A234 Gr. WPB-W, 1 Sch. STD 11 3/4 704258 LNSBABXXAAX5ABAA Stud Bolt, ASME-B18.31.2, ASTM A193 Gr. B7, Thd. 24 The first digits is the item which can be either 1 or 2 characters. The second set is the size, so can be up 4 characters. The this is the same size The fourth is the same size The last needs to be all together - after the fourth space I can pull out the last piece no issue using =MID(A6,FIND("*", SUBSTITUTE(A6," ","*",4))+1,60), but making sure the first and second are correct is my challenge. Ideally, I need 5 columns each with their own data. Any help would be appreciated. TIA

 

[JamesCanale]

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
25	1 8 1452122 PNNPAAADBEDLAKST Pipe, ASME B36.10M, Type E, BE, ASTM A53 Gr. B, 1.6' Sch. STD	1	8	1452122	PNNPAAADBEDLAKST	Pipe, ASME B36.10M, Type E, BE, ASTM A53 Gr. B, 1.6' Sch. STD
26	2 8X6 1462879 BNRCABXXBEAGALST Conc. Red, ASME B16.9, BE, ASTM A234 Gr. WPB-W, 1 Sch. STD	2	8X6	1462879	BNRCABXXBEAGALST	Conc. Red, ASME B16.9, BE, ASTM A234 Gr. WPB-W, 1 Sch. STD
27	11 3/4 704258 LNSBABXXAAX5ABAA Stud Bolt, ASME-B18.31.2, ASTM A193 Gr. B7, Thd. 24	11	3/4	704258	LNSBABXXAAX5ABAA	Stud Bolt, ASME-B18.31.2, ASTM A193 Gr. B7, Thd. 24
Sheet22

Cell Formulas
Range	Formula
B25:F27	B25	=LET(a,TEXTSPLIT(A25," "),aa,TAKE(a,,4),b,DROP(a,,4),c,TEXTJOIN(" ",TRUE,b),HSTACK(aa,c))
Dynamic array formulas.

 

[Sanjeev1976]

You can try :

=HSTACK(TAKE(TEXTSPLIT(A6," "),,4),MID(A6,FIND("*", SUBSTITUTE(A6," ","*",4))+1,60))

 

[Iain Munro]

MrExcelPlayground22.xlsx
	A	B	C	D	E	F
25	1 8 1452122 PNNPAAADBEDLAKST Pipe, ASME B36.10M, Type E, BE, ASTM A53 Gr. B, 1.6' Sch. STD	1	8	1452122	PNNPAAADBEDLAKST	Pipe, ASME B36.10M, Type E, BE, ASTM A53 Gr. B, 1.6' Sch. STD
26	2 8X6 1462879 BNRCABXXBEAGALST Conc. Red, ASME B16.9, BE, ASTM A234 Gr. WPB-W, 1 Sch. STD	2	8X6	1462879	BNRCABXXBEAGALST	Conc. Red, ASME B16.9, BE, ASTM A234 Gr. WPB-W, 1 Sch. STD
27	11 3/4 704258 LNSBABXXAAX5ABAA Stud Bolt, ASME-B18.31.2, ASTM A193 Gr. B7, Thd. 24	11	3/4	704258	LNSBABXXAAX5ABAA	Stud Bolt, ASME-B18.31.2, ASTM A193 Gr. B7, Thd. 24
Sheet22

Cell Formulas
Range	Formula
B25:F27	B25	=LET(a,TEXTSPLIT(A25," "),aa,TAKE(a,,4),b,DROP(a,,4),c,TEXTJOIN(" ",TRUE,b),HSTACK(aa,c))
Dynamic array formulas.

worked perfectly - many thanks

 

[Iain Munro]

You can try :

=HSTACK(TAKE(TEXTSPLIT(A6," "),,4),MID(A6,FIND("*", SUBSTITUTE(A6," ","*",4))+1,60))

worked perfectly - many thanks