Breaking address field

[grumpus]

I have a column of addresses that may or may not end in an apartment number, suite number, or unit number. I need to break that field into 2 fields one with the address and the second column that extracts the apartment, suite, or unit #. What would be the best approach to accomplish this?


Example: Current data in column 1 and need to break out so it looks like columns 2 & 3


I will attach a file as this did not come through correctly.

Edit by Luke M: backticks added to format data properly

[pre]

Current	                         Address1	        Address2
1 Bloomingdale Place Unit 310	1 Bloomingdale Place	Unit 310
1361 S Madlock Ct	        1361 S Madlock Ct
8241 E. Chippewa Trail	        8241 E. Chippewa Trail
1018 S Harvey Ave	        1018 S Harvey Ave
4309 N Ashland Ave Apt A	4309 N Ashland Ave	Apt A
121 Lindenwood #1006	        121 Lindenwood	#1006

[/pre]
http://speedy.sh/NXu33/Addresses.xlsx

 

[Trader273]

This should get you started in the right direction. Of course it can be as simple or complicated as you want to make it.


https://www.dropbox.com/s/meb1j5aoc6ytxkp/Address%202.0.xlsx

 

[grumpus]

Nick! You da man!


This is genius...I thought I was going to have to use text to columns and then reassemble the fields. A tedious process.


Thanks again for taking the time to develop this slick solution.


Grumpus

 

[Trader273]

Not a problem at all. Glad you found it helpful