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.

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

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.

Output: Split Column in Excel by Comma with Convert Text to Columns Wizard


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.

=LEFT(B5,FIND(",",B5)-1)

❷ Then press ENTER.

Formula Breakdown

  • B5 has texts with a comma.
  • FIND(“,”,B5) looks for a comma within cell
  • LEFT(B5,FIND(“,”,B5)-1) returns texts before the first comma appears from the left side.

Combining LEFT and FIND Functions to Split Column in Excel by Comma

❸ After that insert the following formula in cell D5.

=RIGHT(B5,LEN(B5)-FIND(",",B5))

❹ Then hit ENTER again.

Formula Breakdown

  • B5 has texts with a comma.
  • FIND(“,”,B5) looks for a comma within cell B5.
  • RIGHT(B5,LEN(B5)-FIND(“,”,B5)) returns texts after the first comma appears from the right side.

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

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


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.

=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))

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

Formula Breakdown

  • SUBSTITUTE(B5,”,”,”</s><s>”)

The SUBSTITUTE function substitutes the comma in cell B5 with a space.

  • FILTERXML(“<t><s>” &SUBSTITUTE(B5,”,”,”</s><s>”)

The FILTERXML function filters out data separated by spaces.

  • TRANSPOSE(FILTERXML(“<t><s>” &SUBSTITUTE(B5,”,”,”</s><s>”) & “</s></t>”,”//s”))

The TRANSPOSE function splits the data in cell B5 into two different columns.

Apply Dynamic Array Formula to Split Column in Excel by Comma

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

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


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.

Divide Column in Excel by Comma Using Flash Fill

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

Split Column in Excel by Comma Using CSV File

❹ 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

Use VBA Code to Split Column in Excel by Comma

Code Breakdown

  • First I declared 3 variables.
  • Then I ran a Nested For loop.
  • Inside the first For loop, I used the Split and Cells functions to split data by a comma into two separate cells.

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 DataFrom FileFrom Excel Workbook.

Split Column in Excel by Comma Using Power Query

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

Power Query Editor: Split Column in Excel by Comma Using Power Query

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.


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.

Divide Column in Excel by Comma Using Power Pivot

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

= LEFT ( [Country with Capital City], FIND ( ",", Table2[Country with Capital City]) - 1 )

❹ Then hit ENTER.

Formula Breakdown

  • FIND ( “,”, Table2[Country with Capital City])

The FIND function looks for a comma within the column Country with Capital City.

  • LEFT ( [Country with Capital City], FIND ( “,”, Table2[Country with Capital City]) – 1 )

The LEFT function returns data before the comma from the left side.

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.

 

= RIGHT ([Country with Capital City], LEN (Table2[Country with Capital City]) - FIND ( ",", Table2[Country with Capital City]) )

❻ Then hit ENTER.

Formula Breakdown

  • FIND ( “,”, Table2[Country with Capital City])

The FIND function looks for a comma within the column Country with Capital City.

  • LEN (Table2[Country with Capital City])

The LEN function calculates the length of the texts in the column Country with Capital City.

  • RIGHT ([Country with Capital City], LEN (Table2[Country with Capital City]) – FIND ( “,”, Table2[Country with Capital City]) )

The RIGHT function returns the data after the comma from the right side.

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.

Mrinmoy

Mrinmoy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo