In this article 7 sections
- 1. Copy the original separately
- 2. Check for empty columns to the right
- 3. Check the delimiter and preview
- 4. Check dates and leading zeros
- 5. Split with a function in Excel for the web
- 6. Verify the number of rows and values
- 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