Extract text between multiple square brackets into specific cells

TangySauce

New Member
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016
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:
1616724308992.png
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

This seems to do what you described.

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

Book3.xlsx
ABCDEF
1ABCFGHLMNXYZ123
2[ABC];[FGH:blah];[XYZ:<blah]ABCFGH:blah XYZ:<blah 
3[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ123:CowMoo
Sheet869
Cell Formulas
RangeFormula
B2:F3B2=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,SEARCH(B$1,$A2),100),"]",REPT(" ",100)),100)),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEF
1ABCFGHLMNXYZ123
2[ABC];[FGH:blah];[XYZ:<blah]ABCFGH:blah XYZ:<blah 
3[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ123:CowMoo
4[XYZ: abcan];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ: abcan123:CowMoo
Sheet2 (2)
Cell Formulas
RangeFormula
B2:F4B2=IFERROR(REPLACE(LEFT($A2,FIND("]",$A2,SEARCH("["&B$1,$A2))-1),1,SEARCH("["&B$1,$A2),""),"")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
If that might happen, this small adjustment to my formula in Post #2 will fix it:

Book3.xlsx
ABCDEF
1ABCFGHLMNXYZ123
2[ABC];[FGH:blah];[XYZ:<blah]ABCFGH:blah XYZ:<blah 
3[XYZ];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ123:CowMoo
4[XYZ: abcan];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ: abcan123:CowMoo
5[XYZ: ABCan];[ABC:WhoThis];[123:CowMoo];[LMNO]ABC:WhoThis LMNOXYZ: ABCan123:CowMoo
Sheet869
Cell Formulas
RangeFormula
B2:F5B2=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,FIND("["&B$1,$A2)+1,100),"]",REPT(" ",100)),100)),"")
 

TangySauce

New Member
Joined
Mar 25, 2021
Messages
2
Office Version
  1. 365
  2. 2019
  3. 2016

ADVERTISEMENT

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

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEF
1ABCFGHLMNXYZ123
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  abcanCowMoo
5[XYZ: ABCan];[ABC:WhoThis];[123:CowMoo];[LMNO]WhoThis  ABCanCowMoo
Sheet869
Cell Formulas
RangeFormula
B2:F5B2=IFERROR(TRIM(LEFT(SUBSTITUTE(MID($A2,FIND(B$1&":",$A2)+LEN(B$1)+1,100),"]",REPT(" ",100)),100)),"")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,603
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,927
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top