In this article, you will learn 8 different methods to split a column in Excel by a comma with ease.
How to Split a Column in Excel by Comma: 8 Methods
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.
=LEFT(B5,FIND(",",B5)-1)❷ Then press ENTER.
Formula Breakdown ❸ After that insert the following formula in cell D5. ❹ Then hit ENTER again. Formula Breakdown ❺ 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. 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. Formula Breakdown The SUBSTITUTE function substitutes the comma in cell B5 with a space. The FILTERXML function filters out data separated by spaces. The TRANSPOSE function splits the data in cell B5 into two different columns. ❸ Drag the Fill Handle icon from cell C5 to C12. Now you will see the split data into two different columns. 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. 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. 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. Code Breakdown ❹ 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. 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. 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. Formula Breakdown The FIND function looks for a comma within the column Country with Capital City. 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. ❻ Then hit ENTER. Formula Breakdown The FIND function looks for a comma within the column Country with Capital City. The LEN function calculates the length of the texts in the column 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. 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. Download Practice Workbook You can download the Excel file from the following link and practice along with it. 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.
=RIGHT(B5,LEN(B5)-FIND(",",B5))
3. Apply Dynamic Array Formula to Split Column in Excel by Comma
=TRANSPOSE(FILTERXML("<t><s>" &SUBSTITUTE(B5,",","</s><s>") & "</s></t>","//s"))
4. Divide Column in Excel by Comma Using Flash Fill
5. Split Column in Excel by Comma Using CSV File
6. Use VBA Code to Split Column in Excel by Comma
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
7. Split Column in Excel by Comma Using Power Query
8. Divide Column in Excel by Comma Using Power Pivot
= LEFT ( [Country with Capital City], FIND ( ",", Table2[Country with Capital City]) - 1 )
= RIGHT ([Country with Capital City], LEN (Table2[Country with Capital City]) - FIND ( ",", Table2[Country with Capital City]) )
Practice Section
Conclusion
Related Articles
Get FREE Advanced Excel Exercises with Solutions!
