Google Sheets REGEXEXTRACT - Return to one column instead of two?-CodePudding

Using this formula returns values in two separate columns:

=ARRAYFORMULA(REGEXEXTRACT(A2:A, "(. )\?|(. )\"""))

How can I modify this to return everything in the same column? It works if I remove the parentheses (), but then the last character ? or " will appear at the end.

CodePudding user response：

You can use

=ARRAYFORMULA(REGEXEXTRACT(A2:A, "(. )[?""]"))

The pattern matches

(. ) - Group 1 (the group value is actually the return value here): one or more chars other than line break chars as many as possible
[?"] - a ? or " char.

Consider also the following variations:

=ARRAYFORMULA(REGEXEXTRACT(A2:A, "(. ?)[?""]"))
=ARRAYFORMULA(REGEXEXTRACT(A2:A, "([^?""] )[?""]"))

The "(. ?)[?""]" variation matches up to the first occurrence of " or ?. The "([^?""] )[?""]" also matches up to the first occurrence, but it can also match line break chars.

CodePudding user response：

try:

=INDEX(IFERROR(REGEXEXTRACT(A1:A, "^(?:https?:\/\/)?(?:ftp:\/\/)?(?:www\.)?([^\/] )")))