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.

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

On Nov 21, 6:45 pm, Trista @ Pacific <Trista @
wrote:
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.

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.


"Trista @ Pacific" wrote:

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.

Try this.

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

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

--
Biff
Microsoft Excel MVP


"Trista @ Pacific" <Trista @ wrote in
message ...
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.

Thanks!

"bpeltzer" wrote:

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.


"Trista @ Pacific" wrote:

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.