Extract text between multiple square brackets into specific cells

[TangySauce]

Hello everyone,

I hope some experts here could help me with either a formula or script for the following.

The data will exist in a single cell with square bracket delimiters([ ]). My goal is to scan the data and if the text contains a certain phrase, that data is than extracted to a specific cell within the same sheet.

I tried a few variations with IF conditions, ISNumber, MID, SEARCH, FIND, LEFT, RIGHT, etc... but can't quite get the extracts. Its been fun for the past 3 days playing with formulas, but I am at a point where I need help.

Below is a sample of the data:

 

[jtakw]

Hi,

This seems to do what you described.

Please, for future reference, check my signature regarding uploading sample via XL2BB, Thank you.

Book3.xlsx
	A	B	C	D	E	F
1		ABC	FGH	LMN	XYZ	123
2	[ABC];[FGH:blah];[XYZ:<blah]	ABC	FGH:blah		XYZ:<blah
3	[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ	123:CowMoo
Sheet869

Cell Formulas
Range	Formula
B2:F3	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,SEARCH(B$1,$A2),100),"]",REPT(" ",100)),100)),"")

 

[Peter_SSs]

This seems to do what you described.

It may be what the OP wants but I suspect if it is possible to have data like in A4 below then it might not be what is wanted. This would be my alternative.

21 03 26.xlsm
	A	B	C	D	E	F
1		ABC	FGH	LMN	XYZ	123
2	[ABC];[FGH:blah];[XYZ:<blah]	ABC	FGH:blah		XYZ:<blah
3	[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ	123:CowMoo
4	[XYZ: abcan];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ: abcan	123:CowMoo
Sheet2 (2)

Cell Formulas
Range	Formula
B2:F4	B2	=IFERROR(REPLACE(LEFT($A2,FIND("]",$A2,SEARCH("["&B$1,$A2))-1),1,SEARCH("["&B$1,$A2),""),"")

 

[jtakw]

If that might happen, this small adjustment to my formula in Post #2 will fix it:

Book3.xlsx
	A	B	C	D	E	F
1		ABC	FGH	LMN	XYZ	123
2	[ABC];[FGH:blah];[XYZ:<blah]	ABC	FGH:blah		XYZ:<blah
3	[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ	123:CowMoo
4	[XYZ: abcan];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ: abcan	123:CowMoo
5	[XYZ: ABCan];[ABC:WhoThis];[123:CowMoo];[LMNO]	ABC:WhoThis		LMNO	XYZ: ABCan	123:CowMoo
Sheet869

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,FIND("["&B$1,$A2)+1,100),"]",REPT(" ",100)),100)),"")

 

[TangySauce]

Wow! Thank you to both of you are giving a few options to try. Of course both options worked flawless and meets what we are trying to, planning to test against 2k list.
Still working on the @jtakw formula for understanding.

I did a modification to remove the 'search term' from the results. Probably a crude way to do it but it works.

=IFERROR(REPLACE(LEFT($A3,FIND("]",$A3,SEARCH("["&B$1,$A3))-1),1,(SEARCH("["&B$1,$A3)+(LEN(B$1)+1)),""),"")

 

[jtakw]

Based on the modification you made to @Peter_SSs formula, it seems you Actually Don't want the B1:F1 Headers included in the results, contradictory to your sample in OP.

If that's what you actually want, here's my modified formula, if my assumption here is incorrect, just use my formula in Post # 4.

Book3.xlsx
	A	B	C	D	E	F
1		ABC	FGH	LMN	XYZ	123
2	[ABC];[FGH:blah];[XYZ:<blah]		blah		<blah
3	[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]	WhoThis				CowMoo
4	[XYZ: abcan];[ABC:WhoThis];[123:CowMoo];[LMNO]	WhoThis			abcan	CowMoo
5	[XYZ: ABCan];[ABC:WhoThis];[123:CowMoo];[LMNO]	WhoThis			ABCan	CowMoo
Sheet869

Cell Formulas
Range	Formula
B2:F5	B2	=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,FIND(B$1&":",$A2)+LEN(B$1)+1,100),"]",REPT(" ",100)),100)),"")

 

[Peter_SSs]

I did a modification to remove the 'search term' from the results. Probably a crude way to do it but it works.

That's the idea, but it looks like all of your terms appear to have a space after the colon if there is extra text. If that is so, make it a "+2" instead, otherwise we could add a TRIM() function in a similar way that jtakw did.