Mid from cell - need help

[mrteacher]

I thought I had the formula correct to pull the "state" part out of cell A1 but am coming up blank. Below is the example and the formula I modified from an April posting.

Example: Two Rivers, WI 54241

cell C1 has =IF(ISERR(MID(A1,FIND(",",A1)+2,IF(ISERR(FIND(",",A1,FIND(",",A1)+1)),FIND(",",A1),FIND(",",A1,FIND(",",A1)+1))-FIND(",",A1)-1)),"",MID(A1,FIND(",",A1)+1,IF(ISERR(FIND(",",A1,FIND(",",A1)+1)),FIND(",",A1),FIND(",",A1,FIND(",",A1)+1))-FIND(",",A1)-1))

I found a formula that Hayton posted back in April and thought I modified correctly -- was wrong. Can someone give me some insight here.

B1 pulls out the "city" and cell D1 is getting the ZIP... just having "small" problem with the state.

Thank you

 

[SkipVought]

Hi,

=MID(A1,FIND(",",A1)+2, 2)

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

If you're sure all of the entries have a good format (meaning have a comma between city and state) then all you need is this:
[blue]

  =TRIM(MID(A1,FIND(",",A1)+1,3))

[/color]
and for city and zip you should be able to use these:
[blue]

  =TRIM(LEFT(A1,FIND(",",A1)-1))
  =RIGHT(TRIM(A1),5)

[/color]
Or what is it about the data that doesn't match the pattern?

 

[mrteacher]

Thank you very much -- I cannot beleive I was putting so much into this!!!

Another reason that I have have passed on the benefits of this site!!!

~Steve~