PLEASE SHOW ME HOW TO Separate full address INTO COLUMS ADDRESS, CITY, STATE, ZIP

keepgoing

New Member
Joined
Oct 17, 2016
Messages
3
I NEED :rolleyes:TO SEPARATE FULL ADDRESS IN DIFFERENT COLUMS OR COMMAS:
Example:
THE WAY I HAVE THE DATA IS LIKE THIS:
THIS IS THE WAY HAVE THE LARGE DATAADDRESSCITYSTATE ZIP
9737 NW 41 ST SUITE 182 DORAL, FL 33178
19737 Carabana st. Fort Myers, FL 33901

<tbody>
</tbody>
4341 23RD AVE SW NAPLES, FL 34156
1151 KELVIN GROVE PLACE, JACKSONVILLE, FL 32225

<tbody>
</tbody>

14925 sw 42 pl miami, fl. 33188

<tbody>
</tbody>

<tbody>
</tbody>


THANK YOU,
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
state and zip are easy
address and city are impossible because Doral is one word, Fort Myers is 2 , suite 182 or similar is unpredictable, kelvin grove place is 3 words, it could be 2 or 1 if you have a lookup list of every town in USA it would help

I am uk so don't know zips but is FL 33188 always
14925 sw 42 pl miami

<tbody>
</tbody>


<tbody>
</tbody>

 

s_woodbury

New Member
Joined
Feb 26, 2014
Messages
27
Keepgoing,

If there was consistency with the punctuation you could use them to separate the addresses but as oldbrewer mentioned there isn't any predictability. If there is a comma at the end of each city you can identify the position of it in the string. you can also identify the position of spaces and use that to pull data into a new cell. If you did that it would only partially solve your problem for Cities that have more than one word (Ft Myers)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,806
Messages
5,574,424
Members
412,591
Latest member
dawnkotzebue
Top