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, 2022 in Others by Kithuzzz
• 38,010 points
680 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, 2022 by narikkadan
• 63,700 points

Related Questions In Others

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, 2022 in Others by narikkadan
• 63,700 points
452 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, 2022 in Others by narikkadan
• 63,700 points
3,141 views
0 votes
1 answer

How to split columns into two sub columns under their parent column using python in excel

Use str.split: df[['pre', 'post']] = df['Column A'].str.split(r'\s*-->\s*', expand=True) print(df) # Output ...READ MORE

answered Apr 7, 2023 in Others by Kithuzzz
• 38,010 points
1,316 views
0 votes
1 answer
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 4,129 views
0 votes
1 answer
0 votes
1 answer

How to automate split by delimiter in Excel (equivalent of =SPLIT in gSheets)

Multiple ways, one is to use FILTERXML(): Formula in B2: =TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A2," ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 63,700 points
366 views
0 votes
1 answer

How to unmerge multiple cells and transpose each value into a new column in Pandas dataframe from excel file

Try this: df = pd.read_excel("Sample_File.xlsx", header=[0,1,2,3,4,5], index_col = ...READ MORE

answered Jan 8, 2023 in Others by narikkadan
• 63,700 points
1,974 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