How to Split Column in Excel by Comma (8 Quick Methods)

Method 1 – Split a Column in Excel by Commas with the Convert Text to Columns Wizard

• Select your data.
• Go to Data, then to Data Tools, and select Text to Columns.

• The Convert Text to Columns Wizard will appear.
• Select Delimited and hit Next.

• Select Comma for Delimiters and hit Next.

• Insert a cell address as the Destination and hit Finish.

• This will split a column in the place of a comma into two columns.

Method 2 – Combining LEFT, RIGHT, FIND, and LEN Functions to Split a Column in Excel by the Comma

• Insert the following formula in cell C5.

• Press Enter.

• Insert the following formula in cell D5.

• Hit Enter.

• Select cells C5 and D5 and drag the Fill Handle icon down to cells C12 and D12.

• These two formulas will split a column into two columns, separating the values before and after the comma.

Method 3 – Apply a Dynamic Array Formula to Split a Column

• Insert the following formula in cell C5.

• Hit Enter or Ctrl + Shift + Enter.

• Drag the Fill Handle icon from cell C5 to C12.

• You will see the data split into two different columns.

Method 4 – Divide a Column in Excel by Comma Using Flash Fill

• Insert the first value from the original cell in the first result cell (C5).
• When you start entering the value in cell C6, Excel will show suggestions.
• Press Enter to accept.

• Repeat the process for column D.

• You will get the data split into two different columns.

Method 5 – Split a Column in Excel by Comma Using a CSV File

• Select and copy your data.

• Open Notepad and hit Paste.

• Save the file as a CSV file. To save a text file as a CSV file, edit the file extension as .CSV.

• Open the CSV file in Excel and you will see that the data has been automatically split by a comma into two columns.

Method 6 – Use VBA Code to Split a Column in Excel by a Comma

Consider the following dataset where we need to fill the Country and Capital City cells from the information in column B.

• Press Alt + F11 to open the VBA Editor.
• Go to Insert and select Module.

• Insert the following VBA code in the VBA editor.

Sub Split_Column_by_Comma()

Dim xArray() As String
Dim xCount As Long
Dim k As Variant

For h = 5 To 12
xArray = Split(Cells(h, 1 + 1), ",")
xCount = 3
For Each k In xArray
Cells(h, xCount) = k
xCount = xCount + 1
Next k
Next h

End Sub

• Save the VBA code.
• Press the F5 button to run the code.
• This will automatically split the column Country with Capital City into two columns: Country and Capital City.

Method 7 – Split a Column in Excel by a Comma Using Power Query

• Go to Data and choose Get Data.
• Select From File and choose From Excel Workbook.

• From the Navigator window, select your worksheet name.
• Click on Transform Data.

• Go to Transform, then to Split Column, and select By Delimiter.

• The Split Column by Delimiter dialog box will appear.
• Select Comma from the Select or enter delimiter drop-down.
• Hit OK.

• Your data will be automatically split into two columns separated by a comma.

Method 8 – Divide a Column in Excel by a Comma Using Power Pivot

• Go to Power Pivot and hit Add to Data Model.

• Insert your table range in the Create Table dialog box and hit OK.

• Insert the following formula in the top cell of the Calculated Column 1 column.

• Hit Enter.

• The Calculated Column 1 will be filled with the data before the comma.

• Insert the following formula in the top cell of the Calculated Column 2 column.

• Hit Enter.

• The Calculated Column 2 will be filled with the data after the comma appears.

Practice Section

We have included a practice sheet in the download file where you can test out the methods.

Download the Practice Workbook

Related Articles

• Split Date and Time Column in Excel
• How to Split Column by First Space in Excel
• Split Column in Excel by Word
• How to Split One Column into Multiple Columns in Excel Formula