Regexextract over multiple lines within one cell

Question

In Google Sheets, I have this in one cell:

Random stuff blah blah 123456789
<Surname, Name><123456><A><100><B><200>
<Surname2, Name2><456789><A><300><B><400>
Some more random stuff

And would like to match the strings within <> brackets. With = REGEXEXTRACT(A4, "<(.*)>") I got thus far:

Surname, Name><123456><A><100><B><200

which is nice, but it is only the first line. The desired output would be this (maybe including the <> at the beginning/end, it doesn't really matter):

Surname, Name><123456><A><100><B><200>
<Surname2, Name2><456789><A><300><B><400

or simply:

Surname, Name><123456><A><100><B><200><Surname2, Name2><456789><A><300><B><400

How to get there?

Tuberculate

This question should be merged to webapps.stackexchange.com – Blouse

Answer 1

Please try:

=SUBSTITUTE(regexextract(substitute(A4,char(10)," "),"<(.*)>"),"> <",">"&char(10)&"<")

Starting in the middle, the substitute replaces line breaks (char(10)) with spaces. This enables the regexextract the complete (ie multi-line) string to work on, with the same pattern as already familiar to OP. SUBSTITUTE then reinstates the relevant space (identified as being immediately surrounded by > and <) with a line break.

Sachi

While this code snippet may solve the question, including an explanation really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion. – Gathering

@Sachi Looks nice, and thanks for the explanation! I'm gonna try it, but just before I do, a quick inquiry: Since I don't really care about the result being distributed over several lines (which was not obvious from my original question), could this be simplified somehow, ideally using just regex? (please see updated question). – Tuberculate

While it is helpful to know how to substitute out the line breaks, it is specific to an OS and overly complicated when you can use the regex multi-line flags instead. – Sociability

Answer 2

Google sheets uses RE2 syntax. You can set the multi-line and s flags in order to match multiple lines. The following will match all characters over multiple lines in cell A2.

=REGEXEXTRACT(A2, "(?ms)^(.*)$")

Bloodstock

Answer 3

REGEXEXTRACT(A1,"text1(?ms)(.*)text2")

So, in this case:

REGEXEXTRACT(A1,"<(?ms)(.*)>")

Absorption

Can you add context? – Laundryman