Extract any string after colon in Google sheet cell with multiple lines-CodePudding

I am trying to extract everything after a colon : in a cell that has multiple lines within one cell.

Any text: John

Any text: Peter

Any text: Paul

Expected result

John

Peter

Paul

Any help, would be appreciated.

Tried

=REGEXEXTRACT(A1,".*:(.*)")

But only returning first line

CodePudding user response：

use:

=ARRAYFORMULA(IFERROR(BYROW(SPLIT(A:A,char(10)),LAMBDA(ax,JOIN(CHAR(10),REGEXEXTRACT(ax,":\s?(.*)$"))))))

CodePudding user response：

You can use this:

=INDEX(REGEXEXTRACT(split(A1,CHAR(10)),". : (. )"))

Or, if you want it vertically, add TRANSPOSE:

=TRANSPOSE(INDEX(REGEXEXTRACT(split(A1,CHAR(10)),". : (. )")))