Text to columns for a large amount of data

[daniejm]

Hi there,

Thank you in advance for your assistance. I'm a bit of an Excel novice.

I've been given the task of splitting up a large amount of delimiter-separated values that need separating. Unfortunately, the list was provided to me in a Word document and all the data is lumped together (all 246 pages worth - 16k records total).

I can "chunk" the data up for copying / pasting to Excel, use Text To Columns and then splice all the information back together into one list, but that would be a laborious task. I'm hoping there's a VBA code that can be used to make this operation simpler and quicker.

Here's a mocked-up example of the data of how the data was given to me:

John Smith "johnsmith@johnsmith.com"<johnsmith@johnsmith.com>; Jane Smith "janesmith@janesmith.com"<janesmith@janesmith.com>; John Doe "johndoe@johndoe.com"<johndoe@johndoe.com>; Jane Doe "janedoe@janedoe.com"<janedoe@janedoe.com>;

I'd like to use the semi-colon as the seperator and place each record on its own row. The end result would look something like this:

John Smith <johnsmith@johnsmith.com>"johnsmith@johnsmith.com";
Jane Smith <janesmith@janesmith.com>"janesmith@janesmith.com";
John Doe "<johndoe@johndoe.com>johndoe@johndoe.com";
Jane Doe "<janedoe@janedoe.com>janedoe@janedoe.com";

Is it possible, doctor? My weekend would be grateful if it were.

Thanks,

Joe</janedoe@janedoe.com></johndoe@johndoe.com></janesmith@janesmith.com></johnsmith@johnsmith.com></janedoe@janedoe.com></johndoe@johndoe.com></janesmith@janesmith.com></johnsmith@johnsmith.com>

 

[thatoneguy650]

Open word, go to replace, Replace ; with ; ^l this should put all the email addresses on a new line. Save it as a .txt. Open it with excel and do delimited with semicolon as the delimiter and it should all be in rows.

 

[daniejm]

Open word, go to replace, Replace ; with ; ^l this should put all the email addresses on a new line. Save it as a .txt. Open it with excel and do delimited with semicolon as the delimiter and it should all be in rows.

What an easy, effective, and simple trick. Not one I knew of.

Thank you so much, thatoneguy.