How to Paste Comma-Separated Values Into Excel Columns

To paste comma-separated values into separate Excel columns, paste the text into one cell or column first, then select it and go to Data > Text to Columns, choose Delimited, check Comma, pick a destination and select Finish. In Excel for Microsoft 365 or Excel 2024, the formula =TEXTSPLIT(A1,”,”) does the same job.

You copied a line like red,green,blue from an email or a web form, pasted it into Excel, and the whole thing landed in one cell.

Why Pasted Commas Land in One Cell

When you paste plain text, Excel puts each line of the text in its own row, but it doesn’t treat commas as column dividers. So a single line such as 1042,Desk lamp,24.99,In stock sits in cell A1, and five lines like it fill A1 through A5.

The fix is a two-part job: paste, then split. The character that separates the values (here, a comma) is called the delimiter. You have three ways to split on it:

Method	Best for	Excel versions
Text to Columns	A one-time cleanup of pasted text	Excel for Microsoft 365, 2024, 2021, 2019 and 2016
TEXTSPLIT formula	Text that changes, or results you want in rows	Excel for Microsoft 365 and Excel 2024
Data > From Text/CSV	A whole .csv or .txt file rather than a few copied lines	Excel for Microsoft 365, 2024, 2021, 2019 and 2016

Split Pasted Text With Text to Columns

This is the quickest route for a list you copied once. Before you start, make sure the columns to the right of your pasted text are empty, because the split values fill those cells.

1. Click cell A1 (or any empty cell) and press Ctrl+V to paste the copied text. Each line lands in its own row, all in one column.
2. Select the cell or the whole column that holds the pasted text. Select only that one column.
3. Go to the Data tab and select Text to Columns. The Convert Text to Columns Wizard opens.
4. Choose Delimited and select Next.
5. Under Delimiters, check Comma and clear any other boxes you don’t need (Tab may already be checked). The Data preview shows vertical lines where Excel will split each value.
6. Select Next. On this screen you can set a format for each column; see the next section if your data has ZIP codes, part numbers or dates.
7. In the Destination box, pick where the split data should start. Leave it as the original cell to replace the text in place, or choose an empty cell such as C1 to keep the original line for comparison.
8. Select Finish. Each value now sits in its own column.

If something went wrong, press Ctrl+Z right away to put the text back in one cell and run the wizard again. Microsoft’s walkthrough of the Convert Text to Columns Wizard shows the same sequence.

One side effect to know about: after you run Text to Columns, Excel may keep the same delimiter settings for the rest of the session, so later pastes can split on commas automatically. If text starts splitting when you don’t want it to, open the wizard again on any cell, clear the Comma box and finish.

Keep Leading Zeros, Long Numbers and Dates Intact

Excel’s default General format converts anything that looks like a number. That’s fine for prices, but it damages values that only look numeric. ZIP code 02134 becomes 2134, a 16-digit account or tracking number can lose its last digits or turn into scientific notation, and a code like 3-12 may become a date. Excel stores only 15 significant digits, which is why long codes need the Text format described on Microsoft’s leading zeros and large numbers page.

To protect those columns, use the column format screen in the wizard (step 6 above):

1. Click the column in the Data preview that holds the sensitive values. It turns dark when selected.
2. Under Column data format, choose Text.
3. Repeat for any other column that needs it, then set the destination and select Finish.

The same screen has a Date option where you pick the order of month, day and year, which helps when dates arrive in a different order than your regional setting expects. You can also pick Do not import column (skip) for a column you don’t need. Microsoft describes these choices on its Text Import Wizard page, which uses the same layout.

Handle Commas Inside Quotes and Extra Spaces

Some values contain commas of their own, like a city and state. Properly formatted CSV text wraps those values in quotation marks: "Dallas, Texas",75201. In the wizard, make sure Text qualifier is set to the double quotation mark (“) and Excel keeps Dallas, Texas together in one cell instead of splitting it at the inner comma.

If the text has no quotation marks around such values, Excel can’t tell a data comma from a divider. Add the quotes in the source before you copy, or fix the stray columns by hand afterward.

Text copied from emails and web pages often has a space after each comma (“red, green, blue”). Two ways to deal with it:

• Check both Comma and Space as delimiters, then check Treat consecutive delimiters as one. This only works if the values themselves have no spaces.
• Split on Comma only, then clean the leftover spaces with a helper column: =TRIM(B1) removes leading and trailing spaces.

Split With the TEXTSPLIT Function

If you have Excel for Microsoft 365 or Excel 2024, a formula can do the split and update itself whenever the source text changes. Paste the text into A1, then in B1 type:

=TEXTSPLIT(A1,",")

Press Enter and the values spill across B1, C1, D1 and so on. The full syntax is TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]). A few arguments are worth knowing:

• Several delimiters: =TEXTSPLIT(A1,{“,”,”;”}) splits on commas and semicolons at once.
• Empty values: two commas in a row create an empty cell by default. Set ignore_empty to TRUE to skip them: =TEXTSPLIT(A1,”,”,,TRUE).
• Spaces: wrap the result in TRIM to clean them: =TRIM(TEXTSPLIT(A1,”,”)).

Because the result is a spilled formula, the cells to its right must be empty or you’ll see a #SPILL! error. To keep the values but drop the formula, copy the result and use Paste Special > Values. The TEXTSPLIT function reference lists every argument with examples. In older versions such as Excel 2021 or 2019, use Text to Columns instead.

Put the Values Down Rows Instead of Across

Sometimes you want a list like apples,pears,plums to run down column A, one item per row. Two ways to get there:

• TEXTSPLIT with a row delimiter: leave the column delimiter empty and put the comma in the row position: =TEXTSPLIT(A1,,”,”). Each value spills into its own row.
• Split, then transpose: run Text to Columns so the values sit across one row, select them and press Ctrl+C (Cut won’t work for this), right-click the top cell where the list should start and choose the Transpose paste option.

Transpose overwrites anything already in the destination, so pick an empty area. It also isn’t available inside an Excel table; convert the table to a range first, or use the TRANSPOSE function. Microsoft covers this on its transpose rows and columns page. For other ways to fill a block of cells at once, see pasting one value into many Excel cells.

Bring In a Whole CSV File

If the comma-separated data is a file rather than a few copied lines, skip the clipboard. Excel can open a .csv file directly with File > Open, and it splits the columns on its own using your current default data format settings. If CSV files keep opening in another program, you can make Excel the default app for them.

For more control, use the import route instead:

1. On the Data tab, in the Get & Transform Data group, select From Text/CSV.
2. Pick the file and select Import.
3. Check the preview. Change the delimiter if Excel guessed wrong.
4. Select Load to drop the data into a new sheet, or Transform Data to clean it in Power Query first (for example, to set a column to Text so leading zeros survive).

A worksheet holds up to 1,048,576 rows and 16,384 columns, so a file with more rows than that won’t fit on one sheet. Microsoft’s guide to importing and exporting text and CSV files covers both routes.

When the Values Still Won’t Split

• Text to Columns is grayed out or won’t run: the sheet may be protected, or more than one column may be selected. Select a single column and try again.
• The preview shows no split lines: the delimiter in the text isn’t a comma. Data from some regions uses semicolons, and data from other spreadsheets often uses tabs. Check the matching box, or check Other and type the character.
• A .csv file opens as one column: Excel may follow your Windows list separator when it opens CSV files, so if your region uses a semicolon, a comma-separated file can land in one column. Import it with From Text/CSV and choose Comma instead.
• Excel warns that there’s already data in the destination: the columns to the right aren’t empty. Cancel, insert empty columns or choose a different destination, then run it again.

If you’d rather split a value that’s already in your sheet, such as a full name in one cell, the same tools apply; see splitting a cell in Excel for that case.

Comma-Separated Paste Questions

Does this work for tab-separated text copied from a web table?

Text copied from an HTML table or another spreadsheet usually pastes into separate cells already. If it doesn’t, run Text to Columns and check Tab instead of Comma.

How do I split several lines that each hold a different list?

Paste all the lines so they fill one column, select the whole column, and run Text to Columns once. Every row splits with the same settings. With TEXTSPLIT, enter the formula in B1 and copy it down to each row.

If the list comes from a Word table rather than plain text, copying a Word table into Excel keeps the columns without any splitting.