Seongwoo Choi / Journal

What to check before splitting comma-separated values in one Excel cell into multiple columns: empty columns and preserving the original

At a glanceExcel can split comma-separated values into multiple columns with Text to Columns, but Microsoft warns that results overwrite cells to the right. This article lists checks for empty columns, backups, delimiters, formatting, and verification.

Conceptual illustration for What to check before splitting comma-separated values in one Excel cell into multiple columns: empty columns and preserving the original.
AI-generated editorial illustration; not a product screenshot.
In this article 7 sections
  1. 1. Copy the original separately
  2. 2. Check for empty columns to the right
  3. 3. Check the delimiter and preview
  4. 4. Check dates and leading zeros
  5. 5. Split with a function in Excel for the web
  6. 6. Verify the number of rows and values
  7. Summary

If one Excel cell contains comma-separated values such as 'apples, pears, grapes,' you can split them into multiple columns using the 'Text to Columns' feature. However, according to Microsoft Support documentation, the split content overwrites the contents of cells to the right, so the first steps are to secure empty columns and preserve the original. This article presents a checklist based on official documentation, not on results from running the process on an actual file. Menu names may vary depending on the Excel version.

1. Copy the original separately

Copy the column to be split to another sheet or a blank workbook. This is because you need to be able to undo the operation immediately or compare the result with the original if it is not what you expected. Undo (Ctrl+Z) is also available, but after saving and closing, it often cannot be undone, so a copy is more reliable.

2. Check for empty columns to the right

Microsoft Support documentation states that the split content overwrites the contents of cells to the right. For example, if there is other data in column B and you split column A, the values in column B may be deleted. First create enough empty columns immediately to the right of the column being split to hold the results.

3. Check the delimiter and preview

On the Data tab, run Text to Columns, choose 'Delimited,' and then select comma. If the values contain a space after the comma, spaces may remain at the beginning of the results. Microsoft documentation explains that you should use a custom delimiter to split by a comma and a space. In the wizard's preview pane, first check that the results are divided into the correct columns before proceeding to the next step.

4. Check dates and leading zeros

After splitting, a value such as '2026-11-01' may be converted to a date, or a number with leading zeros, such as '00123,' may become '123.' For such columns, check how to choose Text under Column data format in the wizard's final step. This is especially important for values whose leading digits have meaning, such as postal codes, employee numbers, and telephone numbers.

5. Split with a function in Excel for the web

According to Microsoft Support documentation, Excel for the web does not have the Text to Columns wizard. In this case, you can split values with a function such as =TEXTSPLIT(A1,,", "). Because the function does not change the original cell and spills the results into the adjacent range, it is convenient when you need to preserve the original. However, TEXTSPLIT is supported only in some versions, including the latest Microsoft 365, so first check whether the function is recognized in your version.

6. Verify the number of rows and values

After splitting, check whether the number of rows matches the original. If the original has 100 rows but the results contain blank cells or shifted values, check the delimiter or spaces again. For numeric columns, another option is to compare the original total with the total after splitting. It is safest to delete the original only after verification is complete.

Summary

The order is: copy the original, check for empty columns to the right, preview the delimiter, check dates and leading zeros, use a function in Excel for the web, and verify the results last. You may also want to check What to check when the Excel COUNTIF result is 0 for ways to find empty cells, and What to check when ##### appears in an Excel cell when values do not fit in a cell.

Source: Split text into different columns with the Convert Text to Columns Wizard - Microsoft Support , Split a cell in Excel - Microsoft Support

Sources and original article

Read the Korean original on Naver

This article reflects public information available when it was written. Specifications, prices and schedules can change; check the linked official documentation.

This English edition was written and translated with AI assistance, with sources provided. For errors or additional information, please request a correction. Editorial policy