formula to return text in middle of cell

[josh_m]

I have a list that are all formatted the same:
12345 City, ST (status)

I want to pull out the City,ST from the list

I can do a find to find the start position and end position.
=find(" ",A1,1) to find start position (i need to use formula here b/c the # length is variable)
=find(" (",A1,1) to find end position

I just can't figure out how to combine these into 1 formula to return the City,ST piece

thanks in advance for any assistance!

 

[barry houdini]

If your data is in A1 try this formula

=TRIM(REPLACE(LEFT(A1,FIND("(",A1)-1),1,FIND(" ",A1),""))

 

[Jonmo1]

Try

=TRIM(MID(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),C1+1,LEN(A1)))

 

[josh_m]

If your data is in A1 try this formula

=TRIM(REPLACE(LEFT(A1,FIND("(",A1)-1),1,FIND(" ",A1),""))

works great! thanks so much!

 

[josh_m]

Try

=TRIM(MID(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),C1+1,LEN(A1)))

thanks for the formula, I think this would work if the list was in sequential order. but the #'s are random (my fault, didn't think to mention that) I do appreciate you trying!

 

[Jonmo1]

thanks for the formula, I think this would work if the list was in sequential order. but the #'s are random (my fault, didn't think to mention that) I do appreciate you trying!

Woops, in my post I was using several different columns to perform different sections of the task, then combining them into 1 formula.
My preferred way to build complex formulas.

But I forgot to combine 1 part back into the single formula

I had this in C1
=FIND(" ",A1)

Then I posted this (which still included a reference to C1)
=TRIM(MID(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),C1+1,LEN(A1)))


The formula should actually be
=TRIM(MID(SUBSTITUTE(A1,"(",REPT(" ",LEN(A1))),FIND(" ",A1)+1,LEN(A1)))


Anyway, regardless of that..Barry's solution is much better anyway...lol