Split address into multiple cells

Rodney Jorgensen

Active Member
Joined
Nov 9, 2007
Messages
411
I need some help with either formulas or vba code to take a City, State, ZIP from one cell and split it into 3 cells.

So, Say I have this is Range("A1") = Rio Vista CA 94571-0157
how can I split this into 3 cells, say B1:D1?

I do believe I will always be working with the zip+5 and always the 2 digit state code.

Thanks for your help.
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
For B1
Code:
=LEFT(A1,LEN(A1)-11)
For C1
Code:
=MID(A1,LEN(A1)-12,2)
For D1
Code:
=RIGHT(A1,10)

lenze
 
Upvote 0
This was the path that I was thinking also. I forgot the use of LEN to accomplish what I needed. Thanks so much. By the way a change to B1 was needed because it pulled the state also, not just the city. I canged it from -11 to -14.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top