Breakdown Cells in Excel 1

[booboo12]

I have a mailing list that is three lines over one another ina single cell in excel, ex:

Mr. John Doe
123 Street St
City, State ZIP

I need to break this information down completely so that the title, first name, last name, street, city, state, zip all go in to different cells..

Mr. | John | Doe | 123 Street | City | State | ZIP

Is there anyway to do this?

I tried text to columns but you can't separate by returns.

Please help!

 

[SkipVought]

Assuming that your data is in column A, paste this into a module and run from the Tools > Macro > Macros menu.

The parse, using text to columns.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and the central issue...

Sub ReplaceLF()
    Columns("A:A").Replace What:=vbLf, Replacement:="|"
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Yikes.

You'll almost certainly run into problems with folks who have middle names/initials, a two-part last name or are Jr., III, etc.

These extra "name-parts" will cause some records to end up with part of a name that in a column that otherwise contains address information. Everything for these records will be shifted to the right.

But, as a starting point:[ul]
[li]Go to Data > Text To Columns[/li]
[li]Chose Delimited[/li]
[li]Press Next[/li]
[li]Check Space[/li]
[li]Check Other - then, hold down the [Alt] key and type in 0012 using the keypad at the right side of the keyboard[/li]
[li]Press Next - make any changes to format you might need. I'd suggest forcing the Zip code to be Text[/li]
[li]Press Finish[/li][/ul]


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

stupid typos.....

That should be 0010, not 0012. And, come to think of it, if 0010 doesn't work, try 0013. But it definitely isn't 0012.

Sorry about that.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

16hn, oops, I meant...

john,

Why do I keep forgetting this technique?



==> *

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Skip - you are a scholar and a gentleman.

Just not very bright.

KIDDING! I've learned more from you than should be legal without paying you money.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[booboo12]

John,

The text to columns worked perfectly to separate it all out (it's 0010), except for those Mr. & Mrs. John Jacob Ryan III's.... I guess there's nothing I can do about that but concatenate them back together?

 

[SkipVought]

Cuts both ways, my friend! I'd bet its a wash!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

Actually, you'll also run into problems with two-word street names and cities like "East Maple Leaf Ln" and "San Louis Obispo".

So I'd suggest a slightly different approach....

Use the Text-To-columns as before, but only for 0010, not spaces. That will get every record into the format:
[tab]Mr. John Doe | 123 Street St | City, State ZIP

From there, we could use formulas to extract parts of each piece into their own columns. Here are some formulas from Chip Pearson that deal with breaking names apart:

http://www.cpearson.com/excel/FirstLast.htm

Or, if you're familiar with VBA, we could come up with a macro to try to clean things up. If you want to pursue a VBA solution, please start an additional thread in forum707.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.