Excel How to split cells by comma delimiter into new cells

Question

Let's imagine I wish to divide this data, which includes commas as a delimiter, into new cells that will be divided into rows or columns.

The Data	Location
One Museum, Two Museum	City A
3rd Park, 4th Park, 5th Park	City B

How would you do it in either direction? There are lots of methods why are methods provided preferred?

Answer 1

The Excel manual method: choose Text to Column under Data. If you want the data in one column, you can now just copy and paste. This only works well if you're performing it only once and have a tiny data set.

The Power Query method: In this method, you do it once for the data source and then click the refresh button when the data changes in the future. The data source can be almost anything like CSV, website or etc. Steps below:

1 - Pick your data source.

2 - When within excel choose From Table/ Range.

 

3 - Now choose the split method, there is a delimiter and there are 6 other choices.

4 - For this data, I when with custom and use ", "

5 & 6 - To split down you have to select Advanced options. Make the selection.

7 - Close & Load.

I hope this helps you.