How to use mid or left functions to seperate city,st,zip with no commas

excelledsoftware · #5 11-11-2014, 10:52 PM

Quote:

Originally Posted by macropod

For the City: =LEFT(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-5)
For the State: =MID(C2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789"))-3,2)
For the Zip: =RIGHT(C2,LEN(C2)+1-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},C2 & "0123456789")))

Wow Macropod, I never knew you could use curly braces in a search formula like that. I learned something really important today. Thanks.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Suppress comma in "city , stat zip" line when blank	DHammer	Mail Merge	1	05-30-2014 02:43 AM
No inverted commas in word 2013.	tonycrossley	Word	3	01-27-2014 04:55 PM
mail merge treating CITY as DATE	woody	Mail Merge	6	01-30-2013 03:22 PM
Merge to Seperate Documents	joughin	Mail Merge	1	05-17-2012 05:08 PM
Seperate Column widths in one worksheet	miller.3276	Excel	2	01-27-2010 02:12 PM