formula or macro to extract a code in the format “03 30 00” from various text data in multiple variations

[ebrandt]

I need a formula or macro that can extract a code in the format “03 30 00” from various data in text format in multiple variations, such as;

• “033000 CAST-IN-PLACE CONCRETE”
• “Section 03 30 00 CAST-IN-PLACE CONCRETE”
• “03 30 00 CAST-IN-PLACE CONCRETE”
• “ 03 3000 CAST-IN-PLACE CONCRETE” with a leading blank space or spaces
• Section 03 3000 - CAST-IN-PLACE CONCRETE
• Section 12 3661.19 - Quartz Agglomerate Countertops (here I would want “12 36 61”)

The code numbers and text will vary and the codes could begin with numbers between “00” through “50” and sometimes will have more than 6 numbers, but I am just trying to extract the first 6 numbers with the two blank spaces in the format “xx xx xx”.

 

[jtakw]

Hi,

Would this help:

Book1
	A	B
1	033000 CAST-IN-PLACE CONCRETE	03 30 00
2	Section 03 30 00 CAST-IN-PLACE CONCRETE	03 30 00
3	03 30 00 CAST-IN-PLACE CONCRETE	03 30 00
4	Section 03 3 - CAST-IN-PLACE CONCRETE
5	03 3000 CAST-IN-PLACE CONCRETE with a leading blank space or spaces	03 30 00
6	Section 03 3000 - CAST-IN-PLACE CONCRETE	03 30 00
7	Section 12 3661.19 - Quartz Agglomerate Countertops (here I would want 12 36 61)	12 36 61
Sheet187

Cell Formulas
Range	Formula
B1	=IFERROR(TEXT(MID(SUBSTITUTE(A1," ",""),MIN(FIND({0,1,2,3,4,5},SUBSTITUTE(A1," ","")&"012345")),6)+0,"00 00 00"),"")

Formula copied down.

 

[ebrandt]

Yes that works! Thank You!

 

[jtakw]

You're welcome, glad it works for you.

 

[ebrandt]

Is there a similar formula that would eliminate all of the extraneous text at the beginning of each item and leave me with everything else............ie: "12 3661.19 - Quartz Agglomerate Countertops" or "03 30 00 CAST-IN-PLACE CONCRETE"?

 

[jtakw]

Not entirely sure what you mean, do you mean like Column C below?

Book1
	A	B	C
1	033000 CAST-IN-PLACE CONCRETE	03 30 00	033000 CAST-IN-PLACE CONCRETE
2	Section 03 30 00 CAST-IN-PLACE CONCRETE	03 30 00	03 30 00 CAST-IN-PLACE CONCRETE
3	03 30 00 CAST-IN-PLACE CONCRETE	03 30 00	03 30 00 CAST-IN-PLACE CONCRETE
4	Section 03 3 - CAST-IN-PLACE CONCRETE		03 3 - CAST-IN-PLACE CONCRETE
5	03 3000 CAST-IN-PLACE CONCRETE	03 30 00	03 3000 CAST-IN-PLACE CONCRETE
6	Section 03 3000 - CAST-IN-PLACE CONCRETE	03 30 00	03 3000 - CAST-IN-PLACE CONCRETE
7	Section 12 3661.19 - Quartz Agglomerate Countertops	12 36 61	12 3661.19 - Quartz Agglomerate Countertops
Sheet187

Cell Formulas
Range	Formula
B1	=IFERROR(TEXT(MID(SUBSTITUTE(A1," ",""),MIN(FIND({0,1,2,3,4,5},SUBSTITUTE(A1," ","")&"012345")),6)+0,"00 00 00"),"")
C1	=MID(A1,MIN(FIND({0,1,2,3,4,5},A1&"012345")),255)

Formula copied down.

 

[ebrandt]

That works too. Thanks! Saved me a bunch of time.

 

[jtakw]

You're welcome, glad it worked for you.

Consequently, if you do Column C, then you can Extract the 6 digit code from Column C with a simpler formula like below:

Book1
	A	B	C	D
1	033000 CAST-IN-PLACE CONCRETE	03 30 00	033000 CAST-IN-PLACE CONCRETE	03 30 00
2	Section 03 30 00 CAST-IN-PLACE CONCRETE	03 30 00	03 30 00 CAST-IN-PLACE CONCRETE	03 30 00
3	03 30 00 CAST-IN-PLACE CONCRETE	03 30 00	03 30 00 CAST-IN-PLACE CONCRETE	03 30 00
4	Section 03 3 - CAST-IN-PLACE CONCRETE		03 3 - CAST-IN-PLACE CONCRETE
5	03 3000 CAST-IN-PLACE CONCRETE	03 30 00	03 3000 CAST-IN-PLACE CONCRETE	03 30 00
6	Section 03 3000 - CAST-IN-PLACE CONCRETE	03 30 00	03 3000 - CAST-IN-PLACE CONCRETE	03 30 00
7	Section 12 3661.19 - Quartz Agglomerate Countertops	12 36 61	12 3661.19 - Quartz Agglomerate Countertops	12 36 61
Sheet187

Cell Formulas
Range	Formula
B1	=IFERROR(TEXT(MID(SUBSTITUTE(A1," ",""),MIN(FIND({0,1,2,3,4,5},SUBSTITUTE(A1," ","")&"012345")),6)+0,"00 00 00"),"")
C1	=MID(A1,MIN(FIND({0,1,2,3,4,5},A1&"012345")),255)
D1	=IFERROR(TEXT(LEFT(SUBSTITUTE(C1," ",""),6)+0,"00 00 00"),"")