Excel – concatenate all instances of text before delimiter in single cell

I have a column of strings in the following format:

Xxx, yyy, zzz, Need this|AAA, BBB, CCC, And this|Xxx, BBB, CCC, This too...

The number of "|" delimiters is unspecified and varies in each cell in the column. For each of the rows, I need to extract a list of text between each | delimiter and its previous comma.

I found how to get each one individually:

=TEXTAFTER(TEXTBEFORE(cellvalue,"|",", ",-1))

However, I’m not sure how to loop through the X delimiters in each cell to get a list of the text before every delimiter. I’d like these values to be in a list in one cell per row separated by a delimiter.

Bonus if you can add in a method to make the list unique values only!

Appreciate that this would be easier with a different programme but Excel is all I can work with 🙂

>Solution :

You could have tried something like this:

=TEXTJOIN(CHAR(10),1,TEXTAFTER(TEXTSPLIT(A1,,"|"),", ",-1))

To have only unique:

=TEXTJOIN(CHAR(10),1,UNIQUE(TEXTAFTER(TEXTSPLIT(A1,,"|"),", ",-1)))

_{NOTE: I have used line-feeds as delimiter to join( remember to click on wrap text from Home Ribbon), one can use comma , or semi-colons ; as well or any other as per their suit.}

If you have a range of data as below, then wrap the above within a LAMBDA() helper function like BYROW() or MAP(), but I prefer in using the copy down option, since TEXTJOIN() or ARRAYTOTEXT() functions has character limitations, however, may be your records will be within limits, still.

=MAP(A1:A9,LAMBDA(α, 
 TEXTJOIN("; ",1,UNIQUE(TEXTAFTER(
 TEXTSPLIT(α,,"|"),", ",-1)))))