excel address 1 column to multiple

December 4, 2010 at 11:29:44
Specs: Windows 7
I have an excel spreadsheet with multiple address in one column for ex:

Jones Mary 166 New Lane Dallas TX 11111

I need to seperate it into seperate cells by last name, first name, street address, state & zip

How do you do this? I ahve tried text to colums but it seperates the street address into 3 different colums

See More: excel address 1 column to multiple

December 4, 2010 at 12:11:49
How exactly does your data look?

If it is the same as you posted, with spaces between each, the Text to Column should work.

But instead of using Delimited click on Fixed Width
When you press Next, your data will be separated by Arrow Lines, just click on the Arrow Lines and move them to the location you want them and you should get what you want.



Report •

December 4, 2010 at 12:18:11
There really isn't any way to tell Excel where a name stops and an address begins. It's all just text to Excel.

In most cases the best you can do is multiple Text to Columns, maybe using Fixed Width in combination with Delimited to take care of the majority of the addresses and then fix stuff manually.

One thing to try is a Text to Columns, let it split the street addresses into multiple cells, then use something like this to put the addresses back together:

With this:

         C        D        E
1       166      New      Lane

This formula, and then a Copy...Paste Special...Values would get you back to an address in a single cell.

=C1 & " " & D1 & " " & E1

Excel wasn't written to be a very strong "text editor", so trying to get it do exactly what you want with text can sometimes be cumbersome.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

December 4, 2010 at 12:19:45
The data looks like this all in rows under column A

1. Jones Mary 166 Dallas TX 11111
2. Carrieo Samatha 142 West Hartford CT 17892
3. Manifesto Jim 16 Maple Ave Providence RI 13478

I have about 800 rows so the problem with the fixed width is I would have to do 1 at a time is there a way to do all of the rows at one time even if they have different widths for each category (last name, fist name...)

Report •

Related Solutions

December 4, 2010 at 21:48:44
Here's the problem:

With 800 rows, I am assuming that you are going to have names with 2 words, as well as names with 3 (or more) words:

Jones Bob
Smith Jr. Steve
Howe John
Spencer Mary Anne

In addition, I'll bet you'll have multiple formats for the addresses:

16 Maple Ave
25 East Main St
6 West Lane
1232 East Palm Leaf Circle

Your example already showed different formats for cities, 1 word vs. 2:

Dallas TX
West Hartford CT

While there are text functions such as LEFT, RIGHT and MID that can extract parts of those strings, there is no way - I'll say it again - no way that any single formula or Text to Columns operation can deal with all those different formats of names, addresses and cities. There is nothing for Excel to "trigger on" to be able to tell what is a name, an address or a city.

Just as one simple example let's take this string:

Jones Mary 166 New Lane Dallas TX 11111

This formula will produce Jones Mary:

=LEFT(A1,FIND(" ",A1,FIND(" ",A1)+1)-1)

In fact, this formula will work for any name that contains 2 words.

However, if you had this:

Jones Mary Jo 166 New Lane Dallas TX 11111

The formula would still return Jones Mary which not only would be incorrect, but any formula that we write to extract the address that starts after the name would be starting in the wrong position.

Trust me, I deal will mailing lists all the time - some of them thousands of rows long - and there is no "one size fits all" formula or operation that will extract the various parts that you are looking for - except, of course, if every name contains the same number of words, every address contains the same number of words and every city contains the same number of words. Good luck finding a list that meets those criteria!

As I said in Response #2, it's going to take a combination of operations and formulas to get the data set up the way you want.

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

Ask Question