Excel How to split cells by comma delimiter into new cells

0 votes

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?

Oct 2 in Others by Kithuzzz
• 20,660 points
64 views

1 answer to this question.

0 votes

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.

enter image description here enter image description here

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.

answered Oct 3 by narikkadan
• 37,660 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How to get the JasperReports into Excel format with proper alignment

Remove at least one parameter: exporter.setParameter(JRXlsExporterParameter.IS_COLLAPSE_ROW_SPAN,Boolean.TRUE) Note that JRXlsExportParameter is deprecated the correct ...READ MORE

answered Sep 21 in Others by narikkadan
• 37,660 points
182 views
0 votes
1 answer

How to compress Excel buffer into ZIP buffer?

The problem is that something you're using ...READ MORE

answered Sep 23 in Others by narikkadan
• 37,660 points
74 views
0 votes
2 answers
+1 vote
2 answers

how can i count the items in a list?

Syntax :            list. count(value) Code: colors = ['red', 'green', ...READ MORE

answered Jul 7, 2019 in Python by Neha
• 330 points

edited Jul 8, 2019 by Kalgi 3,122 views
0 votes
1 answer
0 votes
1 answer

How to split text values by a delimiter?

The Split function is what you are looking for: =Split(A1, ...READ MORE

answered Oct 11 in Others by narikkadan
• 37,660 points
70 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17 in Others by narikkadan
• 37,660 points
19 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP