left, right, find, mid

I have an address list that has all info in one column -
1234 Spring St New Pottstown AZ 80752
5478 Gallant Forest Ave Apt 432 Media PA 19237

I can't use find (" ") because some of the street names are 1 name, some are 2.
Some of the cities are 2 words, some are 1.
The formatting is not consistent.
There are no delimiters.

How can I get the street address info separated out by 4 columns -- street address, city, state, zip ?

Thank you.

As you state there are no delimiters so it will nearly impossible to decide where/how the addresses should be broken into street /city etc

Streets may be "delimited" by terms such as "St","Ave","Road": State should be doable.

Can you post a small file with a good cross-section of addresses?



Please attach a file (not image) so that respondents (a) do not have to type in data and (b) have data with which test any formulas. Ensure the data is representative of your actual "real world" data.

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the (remove confidential) data.

To Attach a File:

1. Scroll down to the window below your post Additional Options
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.