Getting multiple matches with REGEXEXTRACT in Google Sheets instead of just the first one

Other Questions Google docs
1

I have some text data in cell B3 that looks like this:

Report No. 2010-156 XY, Application for License Renewal from Department of Energy (DOE), Request for Temporary Permit, Metro Power Distribution Corp. (MPDC) - submitted on 11/15/2012

I’m trying to use this formula:

=REGEXEXTRACT(B3,"\([A-Z]+\)")

The problem is that it only captures the first match which is (DOE), but I also need it to get (MPDC). How can I modify my approach to extract all the uppercase letter combinations inside parentheses from the same cell? I tried different variations but keep getting only the first occurrence.

2

REGEXEXTRACT only grabs the first match - that’s just how it works. I’ve run into this before and found REGEXREPLACE is way better for pulling multiple matches at once. Try =REGEXREPLACE(B3,"(.*?)\(([A-Z]+)\)(.*?)\(([A-Z]+)\)(.*)","$2, $4") - should give you “DOE, MPDC” straight up. It uses capture groups to grab exactly what you want and tosses the rest. Only catch is you need to know roughly how many matches you’re expecting, but that’s usually fine with structured report data. If the match count varies a lot, you might need a helper column to count the parenthetical expressions first.

3

yeah, regexextract sucks for this. i mark all matches with regexreplace first, then extract. try =regexreplace(B3,".*?(\([A-Z]+\))","$1|") to separate them with pipes, then split from there. it’s a hack but beats wrestling with multiple regexextract calls.

4

REGEXEXTRACT only grabs the first match - that’s just how it works. For multiple matches, you need to combine REGEXREPLACE with REGEXEXTRACT. First use REGEXREPLACE to isolate all matches, then extract them. Try =REGEXREPLACE(B3,".*?(\([A-Z]+\)).*?(\([A-Z]+\)).*","$1 $2") for your two-match case. But this gets ugly fast when match counts vary. Better approach: use SPLIT + REGEXREPLACE to replace everything except your target patterns with delimiters, then split the result. Something like =TRANSPOSE(SPLIT(REGEXREPLACE(B3,"[^(]*\(([A-Z]+)\)[^(]*","$1,"),",")) - just heads up you’ll get some empty cells to clean up.