How to regex extract alpha portion immediately after an underscore in Google Sheets?

Question

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.

You are asking an off-topic question. Please read On-Topic, How do I ask a good question? and What types of questions should I avoid asking? — DavidPostill, Nov 30, 2016 at 22:02
Please read the tags on your question before you post to the wrong site. — DavidPostill, Nov 30, 2016 at 22:03
How is this off topic? I'm asking about regex within Google Sheets. Two of the tags that were available to me were regex, and google-spreadsheets. What do you have a problem with? — JVC, Nov 30, 2016 at 22:11
OK fine, they shouldn't provide that tag as an option when posting the question then. — JVC, Nov 30, 2016 at 22:13

Amit Agarwal · Accepted Answer · 2016-11-30 20:41:29Z

1

This should work:

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

answered Nov 30, 2016 at 20:41

Amit Agarwal

1,97813 silver badges15 bronze badges

Hot damn, a little slow but works perfectly... thanks! I'm going to wait a bit before awarding best answer just in case someone comes up with an even cleaner, shorter, faster, or otherwise better solution.
– JVC
Nov 30, 2016 at 20:44
Crud, I overlooked another form of data shows up regularly, amending my question to include this form.
– JVC
Nov 30, 2016 at 21:07
Figured out the rest of the solution on my own! =if(iserror(search("_",C1)),REGEXEXTRACT(C1,".* (.*)$"),iferror(REGEXEXTRACT(REGEXREPLACE(C1,".*_", ""),"[A-Z]+"),""))
– JVC
Nov 30, 2016 at 21:36

Add a comment |

1 Answer 1