Google Sheets REGEXEXTRACT – Return to one column instead of two?

November 4, 2022

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.

>Solution :

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.