Need to extract certain text from text string

[Trista @ Pacific]

I'm trying to figure out how to construct a formula to extract certain
information.

The text may look like one of the following:

Safeway Store # 711 - Mill Valley, CA
Jake's Marketplace - Oakland, CA

I am wanting to take the city portion (between the "- " and the "," and put
it in another column. The number of characters will vary and I'm not sure
how to do this. Thanks for any help.

 

[Pete_UK]

With your data in A1, try this formula:

=MID(A1,FIND("-",A1)+1,FIND(",",A1)-FIND("-",A1)-1)

Copy down as required.

Hope this helps.

Pete

 

[bpeltzer]

With the input string in A1:
=TRIM(MID(A1,FIND("-",A1)+1,(FIND(",",A1,FIND("-",A1)))-FIND("-",A1)-1))
This is looking for the comma, then the dash after the comma.
It returns the characters between the two, with any leading or trailing
space characters trimmed off.

 

[T. Valko]

Try this.

Assuming the state is *always* the 2 letter abbreviation.

=MID(A1,FIND("-",A1)+2,LEN(MID(A1,FIND("-",A1)+2,255))-4)

 

[Trista @ Pacific]

Thanks!

With the input string in A1:
=TRIM(MID(A1,FIND("-",A1)+1,(FIND(",",A1,FIND("-",A1)))-FIND("-",A1)-1))
This is looking for the comma, then the dash after the comma.
It returns the characters between the two, with any leading or trailing
space characters trimmed off.