Extracting a word to the left of an opening bracket

**Glenn Kennedy** · 07-08-2012, 04:59 AM

I have strings that contain variable text, but always contains a set of brackets:

Today is the day that Andy Murray (a native of Scotland) plays tennis

or

Sometimes I get thirsty and (when that happens) I reach for a beer

What I want to do is use the text formulae (no good at VBA!!) to extract
"Andy" from the first string, and
"and" from the second string

i.e. the first word before the "(", symbol. I can nearly get it to work, but not quite... It's doing my nut in... Help!!!

**martindwilson** · 07-08-2012, 05:22 AM

dont you mean Murray?
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," (","^")," ",REPT(" ",50)),FIND("^",SUBSTITUTE(SUBSTITUTE(A1," (","^")," ",REPT(" ",50)))-30,30))

**Glenn Kennedy** · 07-08-2012, 05:24 AM

Oh Cr@p. See what I mean about doing my nut in. Of course I mean Murray...

**martindwilson** · 07-08-2012, 05:34 AM

Shorter and works better
=trim(right(substitute(trim(left(a1,find("(",a1)-1))," ",rept(" ",50)),30))

**Glenn Kennedy** · 07-08-2012, 05:40 AM

Originally Posted by martindwilson

Shorter and works better
=trim(right(substitute(trim(left(a1,find("(",a1)-1))," ",rept(" ",50)),30))

Fantastic!! all I have to do is figure out why it works...

Muchas gracias.

**Cutter** · 07-08-2012, 09:57 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save