Comma Separated Values (CSV) data format is quite common and you will often find yourself working with this kind of data in Excel.
In such cases, you may want to divide these values into different cells at each point where there is a comma.
In this article, I am going to show you some simple methods to separate comma-separated values in Excel.
Using the TEXTSPLIT Function to Separate Comma-separated Values
Microsoft Excel has recently introduced the TEXTSPLIT function for us to split a text based on a delimiter.
Below is a table where each respondent entered their favorite three colors. They entered three colors in column B and separated them with commas. I want to split these results into columns C, D, and E.
To separate the comma-separated colors in column B, I can use the below formula in cell C2. Then, I have to copy the same formula to the rows below.
=TEXTSPLIT(B2,",")
The syntax of the TEXTSPLIT function is =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty], [match_mode], [pad_with]).
In this case, I am using only the first two arguments to split the text based on commas.
As the first argument, I have to select the text. So, I select cell B2 as the first argument.
Then, I want to separate the selected text into columns at each comma. So, I enter a comma within quotes for the second argument.
If I want to separate the comma-separated values into multiple rows instead of columns, I can skip the second argument and enter the comma within quotes in the third argument.
Currently, the TEXTSPLIT function is available only for Microsoft 365 users. If you are not a Microsoft 365 user, you cannot apply this formula.
Pro Tip: If I want to separate a text into multiple columns based on multiple delimiters, I can use an array constant {} for the second argument and enter all the delimiters within quotes inside the array constant.
Also read: How to Add Comma Between Names in Excel
Using the Excel Text Functions to Separate Comma-separated Values
If we don’t have the Microsoft 365 Excel version, we can’t use the Excel TEXTSPLIT function.
Then, we have to combine Excel text functions to dynamically separate comma-separated values in Excel.
Below is a table where each respondent entered their favorite three colors. They entered three colors in column B and separated them with commas. Now, I want to split these results into columns C, D, and E.
To separate the comma-separated three colors in column B, I have to use three different formulas.
Formula to Extract the First Color
I can use the below formula to extract the first color from column B.
=LEFT(B2,FIND(",",B2)-1)
I am using the Excel LEFT function to extract the first color.
To get the number of characters dynamically, I use the FIND function. I use a comma within quotes for the “find_text” argument of the FIND function.
The “FIND(“,”,B2)” part of the formula helps to get the number of characters of the first color, including the first comma.
As I want to get only the color (without the comma), I am reducing 1 from the number of characters.
Formula to Extract the Second Color
I can use the below formula to extract the second color from column B.
=MID(B2, FIND(",",B2) + 2, FIND(",",B2,FIND(",",B2)+2) - FIND(",",B2) -2)
I am using the Excel MID function to extract the second color.
I use the FIND function to get the number of characters and starting position to extract data dynamically.
I use a comma within quotes for the “find_text” argument of the FIND function.
As I want to get only the color (without the comma and space), I am adjusting the number of characters by 2 (1 comma + 1 space).
Formula to Extract the Last Color
I can use the below formula to extract the last color from column B.
=RIGHT(B2, (LEN(B2)-FIND(",", B2,FIND(",",B2)+1))-1)
I am using the Excel RIGHT function to extract the last color. To get the number of characters dynamically, I use the FIND function.
I use a comma within quotes for the “find_text” argument of the FIND function.
I am using the LEN function to get the total number of characters of each cell in column B.
A couple of important things to know when using the above formulas:
- I have created these formulas to show you How it works when you have 3 comma-separated values in a cell. If you have more or less, than you will have to adjust these formulas
- Once you have the result, and you do not need the original data, you need to convert your formula result data into static values and only then delete your original data set
Also read: Extract Text After Space in Excel
Using the Excel Flash Fill to Separate Comma-separated Values
If you don’t like Excel functions and you want to separate comma-separated values in Excel, I am sure you will definitely love this method. This method is Excel Flash Fill.
Below is a table where each respondent entered their favorite three colors. They entered three colors in column B and separated them with commas. Now, I want to split these results into columns C, D, and E.
I can use the below steps to separate comma-separated values using the Excel Flash Fill option.
- Go to cell C2 and type the first word before the first comma. So, I type Green in cell C2. (I can even copy and paste the words).
- Go to the cell below that (which is cell C3 in this case) and press “Control + E ” to flash fill. As soon as I use the Flash fill shortcut, I will get the first color for all rows in column C.
- Go to cell D2 and type the word before the second comma. So, I type Red in cell D2.
- Go to one cell below (which is cell D3 in this case) and press “Control + E ” to flash fill. As soon as I use the Flash fill shortcut, I will get the second color for all rows in column D.
- Go to cell E2 and type the last color of cell B2. So, I type Blue in cell E2.
- Go to one cell below (which is cell E3 in this case) and press “Control + E ” to flash fill. As soon as I use the Flash fill shortcut, I will get the last color for all rows in column E.
This method can be used when we have the same number of comma-separated values for all rows. Otherwise, it is difficult for Excel to identify the pattern. So, we may get the wrong results.
You can also use Flash Fill by going to the Data tab and then clicking on the Flash Fill icon, which is in the Data Tools group.
While Flash Fill has worked perfectly in this case, keep this in mind that it works by identifying patterns based on a manual entry that you do in the adjacent column. In case the data is not consistent or it’s a complex data set, Flash Fill may not be able to identify the correct pattern. So it’s always a good idea to double-check your results
Also read: Convert Text to Rows in Excel
Using the Excel Text-to-Columns feature to Separate Comma-separated Values
Let’s say that we want to separate comma-separated values using a non-formula method.
However, the number of values in each cell is not the same. In such a situation, we can use the Excel Text-to-Columns feature to do this task.
Below is a table where each respondent entered their favorite three colors. They entered three colors in column B and separated them with commas. Now, I want to split these results into columns C, D, and E.
I can use the below steps to separate comma-separated values using the Text-to-Column feature.
- Copy all the colors in column B to column C.
- Go to the “Data” tab while you select the pasted values.
- Click the “Text-to-Column” icon (which is in the Data Tools group).
- Select the “Delimited” option in the Convert Text to Columns Wizard – Step 1 of 3. Then, Click the “Next” button.
- Select the “Comma” option in the Convert Text to Columns Wizard – Step 2 of 3. Then, Click the “Finish” button.
Then, Excel will separate the comma-separated colors into multiple columns, as shown below.
This method is not a dynamic option in Excel. For example, if you change the original data (data in column B in this case), it will not be updated in the split data.
One benefit of this method is that your data doesn’t need to be consistent. So you can have three colors in one cell and four in another cell, and it would still split these separated values into different columns.
Also read: Combine Two Columns in Excel (with Space/Comma)
Using the Excel Power Query to Separate Comma-separated Values
We can use another method to separate comma-separated values in Excel. This method is Excel Power Query.
Using this method, we can easily separate comma-separated values into columns or rows.
Below is a table where each respondent entered their favorite three colors. They entered three colors in column B and separated them with commas. Now, I want to split these results into columns C, D, and E.
I can use the below steps to separate comma-separated values using the Excel Power Query.
- Select the comma-separated values, including the heading. So, I am selecting cells B1 to B5.
- Go to the “Data” tab.
- Click the “From Table/Range” icon (Which is in the “Get & Transform Data” Group).
- Check the “My table has headers” option and click the “OK” button.
Then, I can see the “Power Query Editor” window.
- Go to the “Home” tab of “Power Query Editor” and expand the “Split Column” options in the Transform Group.
- Click “By Delimiter” from the expanded options list.
After I select that, I can see the “Split Column by Delimiter” dialog box.
- Select “Comma” as the delimiter and select “Each occurrence of the delimiter” to split at. Then, click the “OK” button.
- Finally, press the “Close & Load” icon of the “Home” tab of the Power Query Editor.
Now, I got a new table that has multiple columns for comma-separated values in a new sheet.
I can copy these separated colors to my original data table.
Additionally, we can use Excel VBA codes also to separate comma-separated values in Excel.
In summary, Excel provides quite useful features to separate comma-separated values. We can select the best option as per our choice.
Other Excel articles you may also like:
