Excel Split Data into Columns by Comma (7 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

excel split data into columns by comma


Excel Split Data into Columns by Comma: 7 Methods

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.

excel split data into columns by comma

  • Next, choose the Delimiter as Comma. Then click Next.

  • Then choose General as Column Data Format. Select the Destination. Finally, select Finish.

excel split data into columns by comma

Excel will split the data.


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.

excel split data into columns by comma

  • Use Fill Handle to AutoFill up to D11.

  • Now click the Auto Fill Options (see image)

excel split data into columns by comma

  • Choose Flash Fill.

Excel will show the cities.

excel split data into columns by comma

  • Similarly, separate the Country.


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)

excel split data into columns by comma

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.

excel split data into columns by comma

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.

excel split data into columns by comma

  • 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.

excel split data into columns by comma

  • 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 split data into columns by comma

  • Excel will split the column under 1 and Address.2 column. Then click Close & Load.

  • Excel will transfer the dataset into a new worksheet.

excel split data into columns by comma

  • Rename the column.


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.

excel split data into columns by comma

  • 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.

excel split data into columns by comma

  • Now, open the file from the location where you saved it earlier.

  • Excel will split the data.

excel split data into columns by comma

  • Now, format as you wish.

Read More: How to Split Comma Separated Values into Rows or Columns in Excel


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.

excel split data into columns by comma

  • 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.

excel split data into columns by comma

Read More: Excel Macro to Split Data into Multiple Files


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.

excel split data into columns by comma

  • 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.

excel split data into columns by comma


Download Practice Workbook


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Akib Bin Rashid
Akib Bin Rashid

AKIB BIN RASHID, a materials and metallurgical engineer, is passionate about delving into Excel and VBA programming. To him, programming is a valuable time-saving tool for managing data, files, and internet-related tasks. Proficient in MS Office, AutoCAD, Excel, and VBA, he goes beyond the fundamentals. Holding a B.Sc in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, MD AKIB has transitioned into a content development role. Specializing in creating technical content centred around Excel and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo