Excel guide

Comma to Column in Excel: Split Text and Join Back (3 Ways)

Last updated: October 16, 2026 · 3 min read

Your list sits in one cell, like red,green,blue, and you want each part in its own column. Or you have a column and want one line with commas. Here are three ways for comma to column and back. Start with Fix 1.

Fix 1

Split comma separated text into columns with Text to Columns

  1. Select the cells with the comma text.
  2. Go to Data and click Text to Columns.
  3. Choose Delimited and click Next.
  4. Tick Comma and remove the other ticks. The preview shows the result.
  5. Click Finish.

Each part now sits in its own column. Excel writes the parts into the columns to the right, so keep them empty. If your list uses semicolons, tick Semicolon instead.

If a part like 02110 loses its zero, choose Text in step 3 of the wizard. More on that in the leading zeros guide.

Fix 2

Split with a formula that works in every Excel

The text is in A2. Put this formula in B2, then copy it to the right and down:

=TRIM(MID(SUBSTITUTE($A2,",",REPT(" ",100)),(COLUMN(A$1)-1)*100+1,100))

The first part lands in B2, the second in C2, and so on. Extra spaces around the parts are removed. Copy the new cells and paste them back with Paste Special, Values if you want plain text. If your Excel wants a semicolon, use ; instead of the comma between the parts of the formula.

Fix 3

Turn a column into a comma separated list with TEXTJOIN

Your names are in A2 to A20. Put this formula in an empty cell:

=TEXTJOIN(",",TRUE,A2:A20)

The result is one line, for example red,green,blue. TRUE skips empty cells, so you get no double commas. Want a space after each comma? Use ", " instead of ",". TEXTJOIN needs Excel 2019 or newer.

Column to comma separated list: copy the result

  • Copy it as text: copy the formula cell and paste it with Paste Special, Values. Now you can use the line in an email or a web form.
  • Join columns instead: first name in one column, last name in another? The Merge Columns tool joins them without a formula.

Comma to column in Excel: before and after

BeforeAfter
red,green,blue in one cellred, green and blue in three columns
red, green, blue in three rowsred,green,blue in one cell

The original cells stay as they are when you use a formula. Only Text to Columns changes the cells in place, so keep a copy of your file first.

Need to join columns without a formula?

The Merge Columns tool runs on your own computer. Your file is never uploaded.

Questions about comma to column in Excel

How do I split comma separated values into columns in Excel?

Select the cells, go to Data, Text to Columns, choose Delimited and tick Comma. Click Finish and each part gets its own column.

How do I turn a column into a comma separated list?

Use TEXTJOIN. The formula =TEXTJOIN(",",TRUE,A2:A20) joins all cells of the column into one line with commas.

Why does TEXTJOIN show #NAME?

Your Excel is too old. TEXTJOIN needs Excel 2019 or newer. Check the spelling too.

My CSV sheet opens in one column. How do I split it?

Select column A, go to Data, Text to Columns and choose Delimited. Tick Comma, or Semicolon if the file comes from a German Excel. Click Finish.

Does Text to Columns change my original data?

Yes. It writes the parts into the cells to the right of your data. Keep a copy of your file, or use the formula in Fix 2.

More Excel guides

← All Excel guides