Regex – How to use regexextract function in Google Docs spreadsheets to get “all” occurrences of a string

google-sheetsregex

My text string is in cell D2:

Decision, ERC Case No. 2009-094 MC, In the Matter of the Application for Authority to Secure Loan from the National Electrification Administration (NEA), with Prayer for Issuance of Provisional Authority, Dinagat Island Electric Cooperative, Inc. (DIELCO) applicant(12/29/2011)

This function:

=regexextract(D2,"\([A-Z]*\)")

will grab (NEA) but not (DIELCO)

I would like it to extract both (NEA) and (DIELCO)

Best Solution

You can use capture groups, which will cause regexextract() to return an array. You can use this as the cell result, in which case you will get a range of results, or you can feed the array to another function to reformat it to your purpose. For example:

regexextract( "abracadabra" ; "(bra).*(bra)" )

will return the array:

{bra,bra}

Another approach would be to use regexreplace(). This has the advantage that the replace is global (like s/pattern/replacement/g), so you do not need to know the number of results in advance. For example:

regexreplace( "aBRAcadaBRA" ; "[a-z]+" ; "..." )

will return the string:

...BRA...BRA