Regexextract over multiple lines within one cell

Question

In Google Sheets, I have this in one cell: ``` Random stuff blah blah 123456789 <123456><100><200> <456789><300><400> Some more random stuff ``` And would like to match the strings within `<>` brackets. With `= REGEXEXTRACT(A4, "<(.*)>")` I got thus far: ``` Surname, Name><123456><100><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><100><200> <456789><300><400 ``` or simply: ``` Surname, Name><123456><100><200><456789><300><400 ``` How to get there?

Accepted Answer

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.

Regexextract over multiple lines within one cell

Solution

Problem