How to parse multiple rows with concatenated data into a single column

Question

I have an excel spreadsheet with delimited information (tracking #s) in various rows:

row a 630521*630621*630215*610517
row b 630522*630611
row c 630531*630651*630265
row d 630524

I would like to organize all rows of tracking #s, separated by a "*", into one column. Can you tell me how this is possible? I have 4,000+ rows like this with up to 21 tracking #s (variable) in each row that I want to convert to one column with multiple rows.

you just want to replace the * with a space and keep what's on the rows the same? — toddmo, Jan 26 '15 at 19:42
i would've done this with vba but we were able to pull it off without vba @TheEngineer — Laurie, Jan 26 '15 at 20:58

score 0 · Answer 1 · answered Jan 26 '15 at 19:59

0

You can do it all in Excel:

Highlight your column
On the Data menu, select Text to Columns => Delimited => Next => Other * => Finish
Then Copy/Paste each of the resultant columns into a single column and sort it to put the blanks at the bottom.

answered Jan 26 '15 at 19:59

Ron Smith

3,241
1
13
16

Thank you. I used # 1 & 2 and then the response from pnuts for #3. – Laurie Jan 26 '15 at 20:57

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

To save step 3. in Ron Smith's answer where you have a LOT of rows, you might add a blank row at the top and on the left, select you entire range (including all the blanks), apply the technique detailed here, delete the columns labelled Row and Column then filter to delete blank rows from column labelled Value.

edited May 23 '17 at 10:26

Community

1
1

answered Jan 26 '15 at 20:23

pnuts

58,317
11
87
139

1

Thank you so much! This was much needed for step 3 from Ron. I appreciate the assistance. – Laurie Jan 26 '15 at 20:56

How to parse multiple rows with concatenated data into a single column

2 Answers2