Akib Bin Rashid
Excel is the most widely used tool when it comes to dealing with huge datasets. We can perform myriads of tasks of multiple dimensions in Excel. Sometimes, we need to split data with commas into columns. In Excel, to split data into columns by comma, we can apply various methods. In this article, I am going to show you 8 effective methods in Excel to split data into columns by comma.
Download Practice Workbook
This is the dataset that I am going to use. Here we have some people along with their Addresses. The Addresses have commas, we will split the Town and Country into separate columns in this article.
7 Methods to Split Data into Columns by Comma in Excel
1. Split Data into Columns Using Text to Column Feature
First, I will show you how to use the Text to Column feature to split data into multiple columns.
STEPS:
- First, select C5:C11. Then, go to the Data tab >> select Data Tools >> select Text to Columns
- Convert Text to Column Wizard will appear. Choose the Delimited Then click Next.
- Next, choose the Delimiter as Comma. Then click Next.
- Then choose General as Column Data Format. Select the Destination. Finally, select Finish.
Excel will split the data.
Read More: How to Split Data into Multiple Columns in Excel
2. Applying Flash Fill to Split Data in Excel
Now, I will use Flash Fill to split data in Excel.
STEPS:
- Write Tokyo in D5.
- Use Fill Handle to AutoFill up to D11.
- Now click the Auto Fill Options (see image)
- Choose Flash Fill.
Excel will show the cities.
- Similarly, separate the Country.
Read More: How to Split Data in One Excel Cell into Multiple Columns (5 Methods)
3. Using a Combination of LEFT, FIND & LEN to Split Data into Columns by Comma
In this section, I will explain how you can split data using the LEFT, FIND, and LEN functions.
STEPS:
- Go to D5. Write down the following formula.
=LEFT(C5,FIND(",",C5)-1)
Formula Breakdown
FIND(“,”,C5) ➤ Returns the position of a character comma (,) in C5.
Output: 6
LEFT(C5,FIND(“,”,C5)-1) ➤ Returns the specified number from the starting of text in C5.
Output: Tokyo
- Then, press ENTER. Excel will return the output.
- Now, use the Fill Handle to AutoFill.
For separating the Country,
- Go to E5. Write down the following formula.
=RIGHT(C5,LEN(C5)-FIND(",",C5))
Formula Breakdown
FIND(“,”,C5) ➤ Returns the position of comma(,) in C5.
Output: 6
LEN(C5) ➤ Returns the number of characters in C5.
Output: 11
RIGHT(C5,LEN(C5)-FIND(“,”,C5)) ➤ Returns the specified position of a character from the end of C5.
Output: Japan
- Now, press ENTER. Excel will show the output.
- Now, use the Fill Handle to AutoFill.
4. Use of PowerQuery to Split Data
Now I will use PowerQuery to split data into columns in Excel.
STEPS:
- Create a table To do so, select the entire range B4:C11.
- Press CTRL + T. An input box will appear. Put the data in your table. Here it is B4:C11.
- Now, go to the Data tab >> select From Table/Range.
- PowerQuery Editor window will pop up. Keep the cursor on the Address column. Then right click your mouse to bring the Context Bar.
- From the Context Bar, select Split Column >> select By Delimiter
- Split Column by Delimiter dialog box will appear. Select the Delimiter as Comma. Then click OK.
- Excel will split the column under 1 and Address.2 column. Then click Close & Load.
- Excel will transfer the dataset into a new worksheet.
- Rename the column.
Read More: How to Split Data in Excel (5 Ways)
5. Converting the Data into CSV File
Now, I will show another method. I will convert the dataset into a CSV (comma-separated values) file first.
STEPS:
- First, copy the column Address into a Notepad page.
- Then, go to File >> select Save As.
- Now, set the name and save the file. Remember, you have to put the .csv suffix in the name.
- Now, open the file from the location where you saved it earlier.
- Excel will split the data.
- Now, format as you wish.
6. Use of VBA to Split Data into Columns by Comma
Now, I will use a VBA code to split data.
STEPS:
- Press ALT + F11 to open the VBA window.
- Then go to Insert >> select Module.
- A new module will open. Write down the following code.
Sub SplitColumn()
Dim SplitData() As String, Count As Long, i As Variant
For n = 5 To 11
SplitData = Split(Cells(n, 3), ",")
Count = 4
For Each i In SplitData
Cells(n, Count) = I
Count = Count + 1
Next i
Next n
End Sub
Code Breakdown
- Here, I have created a Sub Procedure SplitColumn. I used the dim statement to define a variable SplitData as String and i as a variant.
- Then I used a For Loop. 5 to 11 denotes that I will split the data from the 5th to 11th row.
- Next, I used the VBA Split function where n is the row number and 3 defines that the data are in the C column. As Count = 4, the data will be split into column D.
- Again, I used a For Loop to increment the Count.
- Now press F5 to run the code. Excel will split the data.
7. Using the FILTERXML, SUBSTITUTE & TRANSPOSE Functions in Excel to Split Data
Now I am going to use the FILTERXML function along with the SUBSTITUTE & TRANSPOSE functions. This will work for upgraded versions of Excel.
STEPS:
Select D5 and E5. Write down the following formula
=TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(C5,",","</s><s>")& "</s></t>","//s"))
Formula Breakdown
SUBSTITUTE(C5,”,”,”</s><s>”) ➤ This will substitute the comma (,) in the D5 and E5.
Output: “Tokyo</s><s>Japan”
FILTERXML(“<t><s>”&SUBSTITUTE(C5,”,”,”</s><s>”)& “</s></t>”,”//s”) ➤ It returns XML data from the content following XPath
Output: {“Tokyo”;”Japan”}
TRANSPOSE(FILTERXML(“<t><s>”&SUBSTITUTE(C5,”,”,”</s><s>”)& “</s></t>”,”//s”)) ➤ It will transpose the array.
Output: {“Tokyo”,”Japan”}
- Then press ENTER. Excel will return the outputs.
- Then use Fill Handle to AutoFill.
Practice Workbook
Practice makes a man perfect. It is important to practice to internalize any method. That’s why I have attached a practice sheet for you.
Conclusion
In this article, I have demonstrated 7 effective methods in Excel to split data into columns by comma. I hope it helps everyone. And lastly, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.
Related Articles
- How to Split Data into Equal Groups in Excel (3 Methods)
- Excel Macro to Split a Cell into Multiple Rows (With Easy Steps)
- How to Split Data from One Cell into Multiple Rows in Excel (3 Methods)
- Excel Macro to Split Data into Multiple Files (With Simple Steps)
- How to Split Data into Multiple Worksheets in Excel
