0

So I am going around this the long way but what I am trying to do is extract the text in the center of a cell with text on both sides of it.

Example Text is:

Alliance: CRAZY CATS (Neutral)
Alliance: Dark Arts (Yours)
Alliance: Portal (Hostile)

I want to extract everything between : and (

In these cases I only need the name of the alliance the person is in. I tried to do a regedit but didn't completely understand it. I tried a few different formulas but was only able to remove either the first part or the last part and I could push them to two different cells but couldn't get them to work together in the same cell.

JoeJoe
  • 28
  • 1
  • 6
  • If you've tried something, make sure to document it in your question. Otherwise, it'll attract negative feedback from users. – TheMaster Aug 26 '20 at 02:54

2 Answers2

0

You can use REGEX functions, but I find that not everyone understands them. So here, I'll supply a non-REGEX solution.

Supposing your sample data were in A2:A4 (with some header in A1), place the following in B2 (or row 2 of any other column):

=ArrayFormula(IF(A2:A="","",TRIM(MID(A2:A,FIND(":",A2:A)+1,FIND("(",A2:A)-FIND(":",A2:A)-1))))
Erik Tyler
  • 9,079
  • 2
  • 7
  • 11
  • This works PERFECTLY thank you so much... I tried something close to this and I could remove text off the end or the front but not both at the same time... You are awesome thank you.... – JoeJoe Aug 26 '20 at 01:16
  • Glad to help, JoeJoe. If you would, please take a minute to mark one of the answers as "Best Answer." It doesn't matter which to me. It just helps future contributors here to see at a glance that this issue is resolved. Thanks. – Erik Tyler Aug 26 '20 at 01:35
0
=REGEXEXTRACT(A2, ":\s*([^(]+)\s*\(")
  • : literal ":"
  • \s* zero or more space characters
  • () capture group
  • [^(]+ one or more of any character except (
  • \( literal (

See

TheMaster
  • 45,448
  • 6
  • 62
  • 85