Google Sheets – Regex to Extract Alpha Portion After Underscore

google sheetsregexregexextract

Using Google sheets, here's what I'm trying to accomplish:

I have data that comes in like this:

xta_GAM-1a (61095)
xta_SM (61096)
xta_GAM2 (61156)
xta_GAM-1b (61161)
AO CPC ALL GAM
AO CPC ALL PA
AO CPC ALL SM
AO CPC ALL TPP

I want to extract only the first alpha portion after the underscore, or failing that just the last alpha portion, so resulting in this:

GAM
SM
GAM
GAM
GAM
PA
SM
TPP

How can I pull this off?

EDIT: @amit's answer got me most of the way there, and I figured out the last part on my own. Current solution is:

=if(iserror(search("_",C1)),REGEXEXTRACT(C1,".* (.*)$"),iferror(REGEXEXTRACT(REGEXREPLACE(C1,".*_", ""),"[A-Z]+"),""))

I will award to Amit unless anyone has something more compact, elegant, etc.

Best Answer

This should work:

=REGEXEXTRACT(REGEXREPLACE(A1,".*_", ""),"[A-Z]+")

Related Solutions

Google Sheets Regex – Using Regex in Data Validation

The regular expression should be ^[a-zA-Z0-9,./()]*$, where $ at the end means the end of the string. This forces the entire contents to consist of the specified characters.

Also, you don't normally pass a range into data validation formulas: it should be the reference to the upper left corner of the range. For example, if your range is A2:E20, the formula in data validation should be

=REGEXMATCH(TO_TEXT(A2), "^[a-zA-Z0-9,./()]*$")

This formula will then be interpreted for other cells according to the way relative references are treated. This is the same behavior as with conditional formatting.

Google-docs – How to use text from capturing groups in Google Docs regex replace

Little hacky, but it doesn't require an extra add on script and will likely cover 99% of your use cases. You can still use capture groups with RegexReplace and reference in the replace text with $1 or $2. Just split up your regex into two capture groups and concatenate with a random (infrequently used) character like ~. Then you can take the entire returned value from that and replace ~ with a newline:

=SUBSTITUTE(REGEXREPLACE(A1,"(#\d{1,2}[^.]*\.)(\s*)","$1~$2"),"~",CHAR(10))

screenshot

For more info on the regex syntax used in google sheets, see the re2 spec

Best Answer

Related Solutions

Google Sheets Regex – Using Regex in Data Validation

Google-docs – How to use text from capturing groups in Google Docs regex replace

Related Topic