Comma after City

  • Thread starter Thread starter Kreiss
  • Start date Start date
K

Kreiss

Can anyone tell me if there is a formula to place a comma
after the city in this field.

Antioch IL 60002

I've got this far, but can't concat the city back....
=CONCATENATE(",",RIGHT(C2,8))
which gives me..... ,IL 60002

Thanks in advance,
KReiss
 
I dont know why but these are my favorite :) This puts a space in there too
just to make it look better.


=LEFT(A1,FIND(" ",A1)-1)&", "&RIGHT(A1,LEN(A1)-FIND(" ",A1))
 
Here is a slightly bulkier one which handles cities like "san diego" and
"santa fe"

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1,LEFT(A1,FIND(" ",A1)-1)&",
"&RIGHT(A1,LEN(A1)-FIND(" ",A1)),LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)&",
"&RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1)+1)))
 
THANKS FOR THE RESPONSE!
I came across some cities that have spaces in them....for
example...Crystal Lake. I've taken some of your code and
worked it out for this. What I'm trying to do now is get
the zip code. Is there any way to get just the zip code
out. Zip code is not always the same length.

IL 60005
IL 64445000688

Thought if there is a way to start at right and go
till "IL", but I can't figure it out.

Thank you!
KReiss
 
You saw the other formula which covers the cities like Crystal Lake?

Here's a similar one that works for cities like Chicago or Crystal Lake and
pulls out the zip code from the end.

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))-1,RIGHT(A1,LEN(A1)-FIND("
",A1,FIND(" ",A1)+1)),RIGHT(A1,LEN(A1)-FIND(" ",A1,FIND(" ",A1,FIND("
",A1)+1)+1)))
 
Back
Top