Parse multiple values in a cell into separate rows

I'm working with a spreadsheet (v2003) with a lotta raw data, and displaying it in a separate worksheet for executive review. The raw data is fairly easy to deal with, except for one cell that has multiple values of 5 characters, separated by commas (e.g. 4ER9G, 22WE9, 24WK7, etc) and I need to parse those into distinct rows, with all the other values of that row of data remaining the same, or all the other cells from that row remaining blank so that the new rows are only comprised of the 5-character values being parsed. Some of the cells contain 1 or 2 values, but some contain 5 or 6, which complicates a way to accomplish this. If you need more information, please let me know. Thanks in advance.

Thanks for the quick turnaround - I'll go try it now, Appreciate your help!

FABULOUS - works perfectly - wish I could do more than just say 'Thank you'!. Someone's got to figure out how to buy another guy his favorite beverage on the Internet!

Okay Jerry - the parsing works great. However, when I parse the worksheet with all the raw data successfully, then go back to my worksheet that presents the data in a specified format, the 'presentation' worksheet does not update. I have formulas in the 'presentation' worksheet to pull the raw data - say rows 2 through 100. There are 75 rows of raw data. After running the macro to parse the multiple values into separate rows, I now have 90 rows of data, but when I go back to the 'presentation' worksheet - it continues to display only 75 rows of data instead of what is now 90 rows of data. The original data is still displaying and it does not adjust to display the new rows of data. Is there any way to get the 'presentation' worksheet to recalculate and display all 90 rows? F9 does not produce any change. Thanks for any ideas.

You're right of course, removing that portion leaves the entire row blank except for the column of data being parsed (column F). My question was not worded clearly enough.

The column being parsed is column F (column 6), I expected all rows to have one or more values in each cell in that column, e.g. F1, F2, F3, etc. However, a few rows have no value in column F. What was occurring with the original macro was if cell F4 had two values that needed to be parsed, and cell F5 was blank but there is other data in row 5, the second value in F4 was being parsed into F5, but instead of all the other columns in row 5 containing the same values as row 4, they retained the original values of row 5 which should not be associated with the second value from F4. The macro simply took the second value from F4 and placed it into F5 instead of creating a new row for the second value from F4 and copying the other data from row 4. Hope that is more clearly worded.

City State Zip APS # Brand Product # Date
Dallas TX 75231 45 Ford W34FG 7/15/2010
Plano TX 75238 75 Ford G34U8, W42GT 7/12/2010
Ft. Worth TX 76118 45 Ford 6/3/2010
Houston TX 77342 62 Ford Z29HY 6/22/2010

SHOULD BE
Dallas TX 75231 45 Ford W34FG 7/15/2010
Plano TX 75238 75 Ford G34U8 7/12/2010
Plano TX 75238 75 Ford W42GT 7/12/2010
Ft. Worth TX 76118 45 Ford 6/3/2010
Houston TX 77342 62 Ford Z29HY 6/22/2010

Actual result
Dallas TX 75231 45 Ford W34FG 7/15/2010
Plano TX 75238 75 Ford G34U8 7/12/2010
Ft. Worth TX 76118 45 Ford W42GT 6/3/2010
Houston TX 77342 62 Ford Z29HY 6/22/2010

Thank you,
bcullers

Sure - here is a spreadsheet with the sample data. Thanks.

Well, glad its solvable. When I attempted to modify the code in post 7, I royally altered it and Excel kept asking me to 'Debug'. Probably like a lot of users, don't know VBA and therefore struggle with macros. I appreciate any modified code you are able to provide. I should have posted the sample data sooner, thanks again.

I have removed the code from post #7. Sorry for not following forum etiquette. If you have any resolution to the issue posted, I would appreciate a final suggestion, then I'll close this thread.

Thank you,
bcullers

Thank you - I sincerely appreciate the patience and assistance. Works perfectly!

JBeaucaire ,

Thank you for you post. Have you had success with parsing data from multiple columns? You code has column 6 listed, which works well. What if the data comes in from a repeating table (InfoPath) and has married data parallel listed data across multiple columns.

Project Date Budget Actual
1234 12/09/10 12 11
12/16/10 12 9
12/23/10 9 4

The data in date, budget and actual will all reside in a single column.

Look forward to you thoughts.

Hi Jerry - Sorry to revive an old thread but I found your code to be very useful and wanted to thank you for making this public.

I do one minor enhancement - The section where you 'add the values in first column', I want to add the original values back in Column B and C etc.... Right now the code only populates the values in Column A using the conditional loop.

Can you please make some recommendations on how I can display original values in Column B,C...

Thanks!

Originally Posted by JBeaucaire

Try this. I added a step where the macro remembers all the "blanks" in column in column you are parsing and empties them again after all the other blanks are filled in the other columns.