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.

  • 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 '16 at 22:02
  • Please read the tags on your question before you post to the wrong site. – DavidPostill Nov 30 '16 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 '16 at 22:11
  • OK fine, they shouldn't provide that tag as an option when posting the question then. – JVC Nov 30 '16 at 22:13
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. – JVC Nov 30 '16 at 20:44
  • Crud, I overlooked another form of data shows up regularly, amending my question to include this form. – JVC 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]+"),"")) – JVC 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.