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.

 

[felixstraube]

Nice! Didn't think of that. Thanks!

 

[miless2111s]

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

Because I realised that I left out a requirement - that I can apply this result to multiple rows - that was my error; you met the brief, and I learned some new stuff so thank you

I have found a way to do it using VBA, as this involved an AI bot as well as lots of searching last night I assume I can't post the resultant code but it does work. Your method however is elegant and works for the use case I originally stated.

 

[Fluff]

Glad you sorted it & thanks for letting us know.