Forum Discussion
Extract information in a single cell and distribute across multiple cells
Hi All...
I'm looking for a way to take information that's currently being collected in 1 cell and break it out in to multiple cells. I've not been able to come up with a feasible solution yet (primarily exploring vlookups), so hoping someone in this group my have some ideas, as I have roughly 3k scenarios that may need to be broken out up to 5 times.
Attached is an example -the 'source data' is the starting point (with hard returns after each option in the cell). The end result shows how it should look after - removing the 'Option X:' and placing each line in it's own cell.
Thanks in advance for any ideas!
2 Replies
If the data does not need to stay in the same column, this will be relatively easy. First, to get rid of the first portion (the Options), highlight the column, press Ctrl+H to open the Find and Replace dialog. In the Find What box enter "Option ?: " (without the quotes, but including the space after the colon). The question mark is a wildcard for any single character. Leave the Replace With box blank, and click replace all.
If the options can be on the same line, and extend to the right, add 4 columns to the right of the Recommendation column, then highlight the recommendation column. Go to the Data tab, and select Text to Columns. On the first screen, select delimited, then on the second screen select Other and in the box press Ctrl+J (nothing will appear in the box, but you should see the example below it show your data in different columns), and then click Finish.
If they have to be in the same column, you will likely have to do it via VBA, because you're looking at adding roughly 15,000 rows into your table
Greetings!
Extracted info in a single cell and distributed across multiple cells, kindly refer attached file.
Thanks,
