CODEWITHSUNDEEP
regexgoogle-sheets
user19120926
user19120926

Reputation:

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

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.

Upvotes: 1

Views: 231

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626927

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.

Upvotes: 2

Related Questions