Extract each delimited value from column A into its own cell in new column

i everyone,

I have a spreadsheet containing email recipient metadata (email to, cc, bcc) that I am attempting to normalize. Column A contains the email IDs, and column B contains the recipients values, delimited by semi-columns. Below is an example:

Col A |Col B
00001 |Bob Smith [[email protected]];Brett Green [[email protected]]
00002 |[email protected]
00003 |Rob Peters ([email protected]) (Rob*[email protected]) [[email protected]];
00004 |TGARN9 ([email protected]); MIKEROC ([email protected]);*[email protected]

I need a way to take the individual recipient values from Column A, and paste them into a new column so that each cell only contains 1 value, the goal being to have a single column with each individual recipient value. Normally I would just use Text to Columns to put each delimited value in a new column, then manually copy the values from each column into my new target column. However many of these emails contain over 100 recipients (in a few cases up to 800), so doing this manually is out of the question. Is there a formula or VBA solution that can look through each cell in column B, and extract each delimited value into its own cell in a new column, as shown below?

Col C
Bob Smith [[email protected]]
Brett Green [[email protected]]
[email protected]
Rob Peters ([email protected]) (Rob [email protected]) [[email protected]]
TGARN9 ([email protected])
MIKEROC ([email protected])
[email protected]

Give this macro a try...

Please Login or Register  to view this content.

Change the worksheet name...

Please Login or Register  to view this content.