1

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.

1

This should work:

=REGEXEXTRACT(REGEXREPLACE(A1,".*_", ""),"[A-Z]+")
  • 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. – Jonathan van Clute Nov 30 '16 at 20:44
  • Crud, I overlooked another form of data shows up regularly, amending my question to include this form. – Jonathan van Clute Nov 30 '16 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]+"),"")) – Jonathan van Clute Nov 30 '16 at 21:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.