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))
In B5:
=TEXTJOIN(CHAR(10), TRUE, IFERROR(TRIM(TEXTAFTER(TEXTSPLIT(A5, "<<"), ">>")), ""))
(The REGEXTRACT function mentioned by PeterBartholomew1 is currently available to Micorosft 365 Insiders only)
- HansVogelaar, Perfect, thank You. Regarding my post title and the last sentence in the original post - how to apply TEXTSPLIT, if the beginning and ending character is the same:
My standard text. !This is important 1! Another standard text !Important 2! Next standard text
should result in
This is important 1
Important 2
I guess completely different technique?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))
HansVogelaar , it works perfect.
Thank You all the team for your effort, the problem is solved.
