Extracting text between multiple sets of markers and displaying on separate lines

[miless2111s]

I have a formula (in MS Project, pulled into Excel) which has field names shown in the format [text1], each formula will have 1 or more of these fields called into it. For instance:
IIf(([Text19]="Dep" Or [Text19]="Del"),[Text28],[Text27])

I would like to be able to
1) Pull out all the items between the square brackets, once per field; for instance; Text19, Text28, Text27
2) Ideally list the fields on separate rows:
text19
text28
text27
3) Ideally, compare these with another list (MS2Cust) to see if they are custom fields and ignore them if not (so for instance [name] or [duration] would not be listed. This isn't as important as the first or 2nd requirements

I have found various entries on this forum which allow me to extract the data, for instance:
=CONCAT(SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE(A2,"]","["),"[",REPT("[",1000)),SEQUENCE(LEN(A2),,,2)*1000,1000),"[","")) will give me text19text29text27 which isn't ideal as it is hard to spot when the fields start and end.

The amazing formula below (which I am still trying to understand so that I can replace the (xxx) delimiter with [xxx]) gives something very close
=TEXTJOIN(", ",TRUE,INDEX(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(INDIRECT("A1:A"&LEN(A8)))-ROW(A8))*LEN(A8)+1,LEN(A8))),AGGREGATE(15,6,1/(1/(IFERROR(IF(CODE(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(INDIRECT("A1:A"&LEN(A8)))-ROW(A8))*LEN(A8)+1,LEN(A8))))=40,1,FALSE)*ROW(INDIRECT("A1:A"&LEN(A8))),""))),ROW(INDIRECT("A1:A"&SUM(IFERROR(IF(CODE(TRIM(MID(SUBSTITUTE(A8," ",REPT(" ",LEN(A8))),(ROW(INDIRECT("A1:A"&LEN(A8)))-ROW(A8))*LEN(A8)+1,LEN(A8))))=40,1,FALSE),0)))))))

I am using 365 so have access to unique and textjoin etc.

 

[Fluff]

I am using 365

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

=LET(a,TEXTSPLIT(A2,,"["),UNIQUE(TEXTBEFORE(FILTER(a,ISNUMBER(SEARCH("]",a))),"]")))

 

[alansidman]

If you provide some sample data using XL2BB, then we can provide solutions that are specific to your requirements.

 

[miless2111s]

=LET(a,TEXTSPLIT(A2,,"["),UNIQUE(TEXTBEFORE(FILTER(a,ISNUMBER(SEARCH("]",a))),"]")))

Hi, I will update my profile as you suggest. Using the formula generates a #CALC! entry in the cell

 

[Fluff]

That suggests that you don't have [ or ] in A2. so can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[miless2111s]

That suggests that you don't have [ or ] in A2. so can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

My apologies, I didn't spot that it was looking at A2 - I have changed this to A1 and it works with a spill. Very cool. Thank you for the pointer to the XL"BB. The issue comes when I have a list of these items. As I suspect I will need VBA to add additional lines I can modify the supplied formula to
=LET(a,TEXTSPLIT(A1,,"["),TEXTJOIN(",",TRUE,UNIQUE(TEXTBEFORE(FILTER(a,ISNUMBER(SEARCH("]",a))),"]"))))
which will give me a list separated by commas.
Very cool technique.
Is textbefore the same as left or does it have some additional power in this situation?

 

[Fluff]

You asked for a formula that would give the results on separate rows, which my formula does. So why are you now using textjoin?

 

[felixstraube]

Another option:

Book1
	A	B	C
1		Not unique	Unique
2	IIf(([Text19]="Dep" Or [Text19]="Del"),[Text28],[Text27])	Text19	Text19
3		Text19	Text28
4		Text28	Text27
5		Text27
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=LET( a,TEXTSPLIT(A2,"["), b,TEXTSPLIT(a,"]"), TRANSPOSE(DROP(b,,1)) )
C2:C4	C2	=LET( a,TEXTSPLIT(A2,"["), b,TEXTSPLIT(a,"]"), UNIQUE(TRANSPOSE(DROP(b,,1))) )
Dynamic array formulas.

 

[Fluff]

You can shorten that like

=LET(a,TEXTSPLIT(A2,,"["),b,TEXTSPLIT(a,,"]"),DROP(b,1))