How to split cells in an Excel table: detailed steps

Key points of this article

When the content has uniform delimiters (comma, space, hyphen, semicolon), use "Column" to split by delimiter quickly.
When the length of each segment is fixed (such as coding, ID number segments), select "Fixed Width" and manually set the split position in the preview.
Splitting columns will overwrite the existing data on the right, so please leave enough blank columns on the right before operation.
Set the number column that needs to be saved as text to text format before splitting to avoid losing leading 0s and converting long numbers into scientific notation.
The column sorting result is static and will not be automatically updated if the source data is changed; use functions such as LEFT, MID, and FIND when linkage is required.

In Excel, a cell often contains several types of information, such as "Zhang San, 13800000000, Beijing", or "2024-03-15 AM". To sort them into different columns, manual cut-and-paste is slow and error-prone. This article is written for people who need to organize lists, export data, and clean tables. It explains how to use the "split column" function that comes with Excel to split one grid of content into multiple columns.

After reading, you can do three things: determine which splitting method is suitable for your data, complete the splitting step by step, and quickly check whether there are any errors in the results after splitting. The operations mentioned in this article are based on common Excel versions. The names may be slightly different, but the idea is the same.

How to split cells in an Excel table? One-sentence conclusion: Use "split columns" if there is a unified separator, and use formulas only when joint updates are needed.

Answer in one sentence: there is a unified symbol or fixed length in the content, which can be completed in a few seconds with the "separate" function directly; only when you need the result to be automatically updated with the source data, consider using a function.

Your situation.	Recommended practice	Reason
The content is separated by commas, spaces, semicolons, etc.	Separate, select "separator"	Setup is complete once, no need to write formulas
Each segment has a fixed length, such as coding and ID number fragments	Separate, select "fixed width"	Split by position, independent of symbols
The delimiters are not uniform, some use spaces, some use horizontal lines	First use search to replace unified symbols, and then divide into columns	After the symbols are unified, the split will not be misplaced.
The source data will change, and the results need to change accordingly.	Use LEFT, MID, FIND and other functions	The breakdown produces static results.
Only split one or two cells	Manually enter or copy and paste	The amount of operation is small, and it is faster than the setting.

Before you start: Confirm these points first

Taking a minute to check before you start can save a lot of rework.

Whether there is data on the right side. When dividing into columns, the separated content will be filled in the cells on the right in turn, and the original data will be overwritten. If there is already content on the right, you need to insert enough blank columns first.
Whether the delimiters are uniform. Try to have only one type of separator in the same column. If some cells use commas and some use spaces, the columns will not be aligned after splitting. You can first filter to see how many ways to write.
Whether there are long numbers or leading 0's. If phone numbers, ID numbers, and numbers starting with 0 are treated as numbers, the end or beginning will be lost or converted into scientific notation. Such columns should be formatted as text before splitting.
Whether to retain the original data. Column splitting directly rewrites the original table. It is recommended to copy a worksheet first, or copy the original columns to another place, so that you can roll back if you make a mistake in splitting.
Whether there are merged cells. Merging cells will cause the split range to be irregular, and the results are easily misaligned. Before splitting, try to cancel the merge and fill in the contents of each grid.

Steps to split cells by delimiter in Excel

The following takes "a list of names, phone numbers, and cities mixed together and separated by commas" as an example.

Back up the original table. Copy a worksheet, or copy the columns to be split to a blank area, retaining the original data.
Leave blank columns. Insert enough blank columns to the right of the column to be split, at least equal to the number of possible segments after splitting minus one. For example, if "name, phone number, city" is split into 3 paragraphs, 2 columns need to be left blank on the right side.
Select the data area to be split. Select only the column that needs to be split, do not select irrelevant columns together.
Turn on the column sorting function. Find "Column" in the data-related ribbon of Excel, and it will pop up a step-by-step wizard.
Select the splitting method. If the content is separated by symbols, select "Delimiter". If the length of each paragraph is fixed, select "Fixed Width", and then click Next.
Set the separator or segmentation position. When pressing separator mode, check the actual symbols used, such as comma, space, semicolon, and Tab key; if the symbol is not in the list, check "Others" and fill in the corresponding characters. Confirm whether each column is split correctly in the preview area.
Set the column data format and target area. "Text" can be selected in the phone column to avoid changes in number format; the corresponding date format can be selected in the date column. Fill in the starting cell of the split result in the target area, and then click Finish.

Check each item after completion

Don’t rush to close the file after the split is complete, check the following points one by one.

Check items	How to see	What to do if there is a problem
Whether the number of columns is correct	See if the contents of each line fall into the expected column.	Check if the delimiter is omitted after undo, or if a line has an extra comma
Is there any content overwritten?	Compare the original table with the split column on the right	Insert more blank columns after undo, and then re-sort
Is the number complete?	Check the phone number, the number of digits, and the first 0	Format the column as text before splitting and re-split it
Is the date correct?	See if the date is displayed as the expected year, month and day.	Select the corresponding date format for the column in the third step
Does the number of rows match?	Check the number of non-blank lines by filtering or counting	Check for dislocation caused by spaces, line breaks, or merged cells

If some lines are found to be wrong when checking, it is usually the delimiters of individual cells that are written differently. It is safer to find these lines, write them uniformly and open them again than to modify them manually one by one.

# Core comparison

Method	Suitable for the scene	Advantages	Limitation
Separate function	One-time split, uniform delimiter or fixed length	The operation is intuitive and can be completed in several steps.	The result is static and the source data will not be updated when it changes.
LEFT, MID, FIND and other functions	Need linkage update, split rules are complex.	The result changes automatically with the source data	Need to write a formula, there is a threshold for beginners
Copy and paste manually	The amount of data is very small	No learning function required	The speed of data is slow and easy to make mistakes.

# frequently asked questions (FAQ)

1. What if the separated content becomes date or scientific counting?

This is usually because columns are not formatted at the time of division. After the operation is undone, the column is formatted as text and then re-sorted. The wrong number has been displayed and needs to be re-split from the original data. Directly modifying the display format will not restore the original value.

2. What does a fixed width split mean? When do you use it?

Fixed width is segmented according to character position, which is suitable for the content with fixed length of each segment, such as the first few bits of coding represent the region and the middle bits represent the date. In the preview area of the sorting wizard, click the location line to set the syncopation point, drag or double-click to adjust and delete.

3. There are many different delimiters in a box, can you break them all at once?

Sure. In the second step of the sorting wizard, check multiple delimiters at the same time, such as commas and spaces, and Excel will use both symbols as separators. The premise is that these symbols do only serve as separators and are not part of the content.

4. Will the breakdown change the other contents of the original document?

The breakdown affects only the columns you select and the target area. It overwrites what already exists in the target area, but does not change the data outside the selection. Therefore, backing up the original table and leaving blank columns are effective protection measures.

5. What should I do if I want the result to change automatically with the source data after the split?

The breakdown generates static values, and the source data is not updated synchronously after modification. If you need linkage, you can use text functions such as LEFT, MID, FIND, and so on, in the new column to extract content by location or symbol. This kind of method needs a certain formula basis, and when the rules are complex, we can first use breakdown, and then check against the formula.

# Summary

The core of the Excel split cell is the "sorting" function: a unified symbol is split by a separator, and the length is fixed by width. Leave blank columns before splitting, format numbers, keep backups, and check column numbers, numbers and dates one by one after splitting. Only when you need to update the results automatically, you need to use the function instead, so that you can choose the appropriate method according to the situation of the table.

Open OmniDoc and process your form file