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

In this article, you will learn 8 different methods to split a column in Excel by a comma with ease.

Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

8 Methods to Split a Column in Excel by Comma

1. Split Column in Excel by Comma with Convert Text to Columns Wizard

To split a column by comma using the Convert Text to Columns Wizard,

❶ Select your data and then

❷ Go to Data ➤ Data Tools ➤ Text to Columns.

The Convert Text to Columns Wizard will appear.

❸ Select Delimited and hit Next.

❹ Select Comma as Delimiters and hit Next again.

❺ Insert a cell address as Destination and hit Finish.

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

2. Combining LEFT, RIGHT, FIND, and LEN Functions to Split Column in Excel by Comma

You can use two formulas using the LEFT, RIGHT, FIND, and LEN functions to split columns.

❶ At first insert the following formula in cell C5.

❷ Then press ENTER.

❸ After that insert the following formula in cell D5.

❹ Then hit ENTER again.

❺ Select cells C5 and D5 and drag the Fill Handle icon up to cells C12 and D12.

These two formulas will split a column in place of a comma into two columns.

Read More: How to Convert Text to Columns in Excel (3 Cases)

3. Apply Dynamic Array Formula to Split Column in Excel by Comma

The dynamic array formula used in this method can automatically split a column having commas into columns.

To use it,

❶ Insert the following formula in cell C5.

❷ Then press ENTER.

The formula is an array formula, it will automatically keep split data in cell D5, nevertheless the formula was applied in cell C5.

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

Now you will see the split data into two different columns.

Read More: How to Split One Column into Multiple Columns in Excel

4. Divide Column in Excel by Comma Using Flash Fill

You can use the Flash Fill feature to divide a column quite easily.

❶ Start inserting data before the comma encounters in the Country column.

❷ After inserting data in two consequent cells, Excel will show suggestions. Press ENTER to accept.

❸ Now start inserting data after the comma in the column Capital City.

❹ After inserting data in two consequent cells, Excel will show suggestions. Press ENTER to accept again.

Now you will get your data split into two different columns.

5. Split Column in Excel by Comma Using CSV File

The CSV file whose elaboration is Comma Separated Value can split a column by a comma automatically.

Here’s how it works.

❶ Select and copy your data first.

❷ Open Notepad and Paste them there.

❸ Now save the file as a CSV file.

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

❹ Now open the CSV file and you will see that the data has been automatically split by a comma into two columns.

6. Use VBA Code to Split Column in Excel by Comma

Look at the following blank columns i.e. Country and Capital City respectively.

We will use a VBA code to split data from the column Country with Capital City.

❶ First press ALT + F11 to open the VBA Editor.

❷ Then go to Insert ➤ 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.

❺ Now press the F5 button to run the code.

This will automatically split the column Country with Capital City into two columns which are Country and Capital City.

7. Split Column in Excel by Comma Using Power Query

Follow the steps below to split a column in Excel by a comma using the Power Query.

❶ Go to Data ➤ Get Data ➤ From File ➤ From Excel Workbook.

❷ From the Navigator window, select your worksheet name having the data to split.

❸ Then click on Transform Data.

❹ Now go to Transform ➤ Split Column ➤ By Delimiter.

The Split Column by Delimiter dialog box will appear.

❺ Select Comma from the Select or enter delimiter drop-down.

❻ Then hit OK.

Now your data will automatically split into two columns separated by a comma.

Read More: How to Split Column in Excel Power Query (5 Easy Methods)

8. Divide Column in Excel by Comma Using Power Pivot

You can use the Power Pivot feature in Excel to split a column by a comma.

For that,

❶ Go to Power Pivot ➤ Add to Data Model.

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

❸ Now insert the following formula in the top cell of the Calculated Column 1 column.

❹ Then hit ENTER.

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

❺ Now insert the following formula in the top cell of the Calculated Column 2 column.

❻ Then hit ENTER.

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

Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file. Where you can practice all the methods discussed in this article.

Conclusion

To sum up, we have discussed 8 methods to split a column in Excel by a comma. You are recommended to download the practice workbook attached along with this article and practice all the methods with that. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.

Related Articles

• How to Split Column by First Space in Excel (with Easy Steps)
• Split Date and Time Column in Excel (7 Easy Methods)