Split Company Name and Address into multiple Cells

[snuffnchess]

I am working through a listing of close to 1000 companies, and the format given to me on it is really no bueno.

All of the data is located in column A without any type of text dilineator between the Company name, Address, etc.... except for a space.

But because each line has a different number of words/spaces, Text to Columns based off of space wont work. Any thoughts on how to split?

Ideally, would like columns

Company Name
Address
Address 2 (if there is a Suite)
City
State
Zip
Phone number

Even if we could just get Company name and address separated that would work.

Logically, I think it would have to be something like if there is a Space followed by a number, then move it to the following cell... but I've no clue how to split via macro

Sample file shows one line of how I have received the info.

[Glenn Kennedy]

Not easy... not made easier with ONE sample, which probably is not representative of your real data. Towns with two word names, suites always called suites.... etc.

Please upload a representative sample, not one.

[snuffnchess]

Here is a bigger sampling

[Glenn Kennedy]

First issue:

AEgis Technologies M 410 Jan Davis Drive, NW Huntsville, AL 35806-4545 256-922-0802

Is the M part of the name or part of the address???

[snuffnchess]

First issue:

AEgis Technologies M 410 Jan Davis Drive, NW Huntsville, AL 35806-4545 256-922-0802

Is the M part of the name or part of the address???

Part of the name

[Glenn Kennedy]

Second issue: What zip code do you want:
ODU-USA, Inc. 4010 Adolfo Rd Camarillo, CA 93012-6793 Tel: 805-484-0540

[snuffnchess]

5 digit zip code is all that is needed for the US. 6 if Canada... but I can separate that out with text to columns

[Glenn Kennedy]

I had a look at this using formulae. It doesn't handle Italian addresses at all!! (row 21).

I do not know how to deal with 2-word city names. I can pull a one-word city name from column C to give you an address and a city name in two columns... but Boca would appear in the address and Raton would be the city name. any ideas??

it doesn't handle entries that have no tel. no. well. Unfortunately, they are not all preceeded by "Tel:", so I can't use that.

See sheet....

[snuffnchess]

WOW!File looks great...... I appreciate it, and I suppose need to get better with arrays.

How to split out the city... that is a tough one.

And will take some playing around.... I suppose to just at minimum get the 1 word city that can just do something like

Please Login or Register  to view this content.

Right?

One other thought That I have is with a simple "find/replace" If we convert things like "Suite" to ",Suite" then can do text to column searching for the punctuation delimiter and get those into their own column.

Then at at least that info gets pulled out separately, and can pull the city names from those cells? It wont be perfect as it wont capture things like "floor" but I think those exceptions are something that can be handled manually.

Your thoughts?

[Glenn Kennedy]

Red face... I forgot about you.

This is as far as I can go with this...I hope it's helped a bit...

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.