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.

4
  • 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? Nov 30, 2016 at 22:02
  • Please read the tags on your question before you post to the wrong site. 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

1 Answer 1

1

This should work:

=REGEXEXTRACT(REGEXREPLACE(A1,".*_", ""),"[A-Z]+")
3
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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