Forum Discussion
Solved
How to extract multiple occurrences of a text enclosed between two same characters
Dear colleagues, I have a cell with plenty of text. Some part of the text is enclosed between tags >> and <<. The task is to extract all these strings to one cell, separated by new line. Notes ...
I hope this works for you (with the text in A5):
=LET(w, TEXTSPLIT(A5, "!"), n, COUNTA(w), s, SEQUENCE(, n), f, FILTER(w, ISEVEN(s)), TEXTJOIN(CHAR(10), TRUE, f))
Just to add some thoughts on modern Excel. I am coming to be of the opinion that spreadsheet formulas are best presented as Lambda functions rather than having the formula details exposed. In that case HansVogelaar's formula might be
ImportantText1λ
= LAMBDA(string,
LET(
newLine, CHAR(10),
extracts, IFERROR(TRIM(TEXTAFTER(TEXTSPLIT(string, "<<"), ">>")),""),
TEXTJOIN(newLine, TRUE, extracts, "")
)
);whilst mine might be
ImportantText2λ
= LAMBDA(string,
LET(
newLine, CHAR(10),
regex, "[\w|\s]*(?=\<)",
TEXTJOIN(newLine,,REGEXEXTRACT(string, regex, 1))
)
);The worksheet formulas would be
= MAP(notes, ImportantTextλ)Any change to the formula made by the developer need not even be visible to the user (unless they are the same person of course) so the sheet may be updated when one chooses using the AFE (Advanced Formula Environment).
As variant
=TEXTJOIN(
CHAR(10),,
FILTERXML(
"<t>" &
SUBSTITUTE( SUBSTITUTE(A5,">>","<s>"),"<<","</s>") &
"</t>",
"//s" )
)