Have you ever looked at a messy Excel sheet and wondered, “How do I split this data into separate cells?” It can be challenging when you have full names, addresses, or product information all in one cell. Whether you are dealing with text separated by commas, spaces, or patterns, Excel gives you several easy ways to split and clean your data, without any coding. In this article, we will learn all the smart ways to split the cells in Excel using delimiters, fixed width, Flash Fill, formulas, and Power Query.
Table of Contents:
What Is Cell Splitting in Excel?
Cell splitting in Excel refers to the process of dividing the content of a single cell into multiple cells, but Excel does not offer a direct “Split Cell” feature. Instead, you can simulate or achieve the effect of splitting a cell using various methods such as Text to Columns, manual splitting, or using formulas. In Excel, every cell has its unique address (e.g., A1, B2), and it does not mean physically dividing one cell into two smaller sub-cells, as in a table. Rather, it means splitting the content of one cell into multiple cells, usually adjacent ones in the same row or column.
Splitting a cell is useful when:
- You have full names in one cell and want to separate them into first and last names.
- You want to separate data like “City, State” into two columns.
- You are importing data from an external system and need to break it into multiple fields.
There are various methods that can be used to split a cell in Excel. Let us understand them in detail.
Master Data Analytics - Accelerate Your Future
Enroll Now and Transform Your Future
Split Cells in Excel Using a Delimiter
A delimiter is a character that separates data in a cell, like a comma, space, semicolon, or tab. If you have a list like “Intellipaat,Bangalore” in one cell and want “Intellipaat” and “Bangalore” in separate cells, you can use Excel’s Text to Columns tool to split it.
Steps to Split the Cells Using the Delimiter
Step 1: Highlight the Cells You Want to Split
Click on the Cell you want to split, and after that:
Step 2: Click the “Data” Tab
Go to the top menu in Excel. Then, click the “Data” tab to switch to the data-related tools.
Step 3: Click “Text to Columns”
In the Data Tools group, click on the “Text to Columns” button. This opens the Convert Text to Columns Wizard.
Step 4: Choose the “Delimited” Option
In the wizard, select “Delimited”, i.e., your text is separated by characters like commas or spaces as you will choose. Then, click Next to continue.
Step 5: Select Your Delimiter(s)
Choose the delimiter that matches your data. Choose a comma if the values are like “Intellipaat, Bangalore”. Also, you can check multiple boxes if needed.
Step 6: Pick a Column Format Option
You can choose how Excel should treat the new columns:
- General (default): Automatically detects numbers or text
- Text: Keeps everything as plain text
- Date: If splitting dates (choose the correct format)
Step 7: Click “Finish”
Once everything is set, click on Finish. Your data will be split into multiple columns based on the delimiter you selected.
Split Cells in Excel Using the Fixed Width Option
The Fixed Width method is used when the data in your cell has a consistent spacing pattern. For example, if the first five characters of the text are always the first name, and the next 6 are always the last name. Then instead of splitting based on a specific character, like a delimiter, this method splits the text based on position.
Steps to Split the Cells Using the Fixed Width Option
Step 1: Select the Cells You Want to Split
Click to highlight the cell that contains the text you want to split.
Step 2: Go to the “Data” Tab
On the top menu ribbon, click the “Data” tab to access data tools.
Step 3: Click “Text to Columns”
Under the Data Tools group, click on “Text to Columns”. This will open the Convert Text to Columns Wizard.
Step 4: Choose the “Fixed Width” Option
In Step 1 of the wizard, select “Fixed width”. Then, click on Next.
Step 5: Set the Break Lines
Excel will display a preview of your text with a ruler. Then, click inside the Data Preview box where you want to insert a column break, for example, after five characters. You’ll see a vertical line appear where the split will occur.
Step 6: Click “Next” to Continue
After setting break points, click Next.
Step 7: Choose a Column Format
Select how Excel should format each column, i.e., General, Text, or Date.
Step 8: Click “Finish”
Excel will now split the cells based on the positions you defined.
Split Cells In Excel Using Flash Fill
Flash Fill is a smart tool in Excel that automatically fills in values when it detects a pattern. It is useful for splitting the text like names, email addresses, or dates without using formulas or menus.
Note: Flash Fill is available in Excel 2013 and later versions.
Steps to Split the Cells In Excel Using Flash Fill
Step 1: Enter Data in a Column
Start with your original data in one column. For example, you want to split Full Name into First Name and Last Name.
Step 2: Type the First Part Manually
In the adjacent column (B1), type the first name from the first cell
Step 3: Use Flash Fill
- Click on the next cell.
- Press Ctrl + E (shortcut for Flash Fill)
Step 4: Repeat for Last Names
Or you can go to the Data tab, then click on Flash Fill in the Data Tools group. Excel will automatically fill in the remaining first names by detecting the pattern
Get 100% Hike!
Master Most in Demand Skills Now!
When you want dynamic splitting of cells in Excel, formulas are the best choice. They automatically update if the original data changes, unlike Text to Columns or Flash Fill, which are one-time actions. You can use various text functions like:
- LEFT(), RIGHT(), MID()
- FIND() or SEARCH()
- LEN()
- TEXTSPLIT()
For example, let’s split a full name into first and last names in Excel. You have names in column A as
Let’s split them into first and last names using formulas.
You can use the following formula to extract the first name of A1 in B1.
=LEFT(A1, FIND(" ", A1) - 1)
In the above formula,
- FIND(” “, A1) locates the position of the space.
- LEFT(A1, …) extracts characters from the start up to the space.
You can use the following formula to extract the last name of A1.
=RIGHT(A1, LEN(A1) - FIND(" ", A1))
In the above formula,
- LEN(A1) gives the total length of the string.
- FIND(” “, A1) locates the space.
- RIGHT(A1, …) extracts from the end, skipping the first name.
Note: These formulas assume that names are separated by a single space.
Split Cells Into Columns or Rows Using Power Query
Power Query is a powerful data transformation tool in Excel that allows you to split and clean the data with more control than standard Excel features. It can split cell contents into multiple columns or even multiple rows, based on a delimiter or fixed length.
Follow the steps below to use Power Query
- Step 1: Select your data (e.g., A1:A2).
- Step 2: Go to the “Data” tab in the ribbon.
- Step 3: Click “From Table/Range”.
- If prompted, confirm the table range and check “My table has headers” if applicable.
- Excel will open the Power Query Editor.
Option 1: Split Cells Into Columns
If you want to split data into columns, follow the steps below
Step 1: In Power Query, select the column with the values (e.g., Fruits).
Step 2: Click on the “Split Column” dropdown in the toolbar.
Step 3: Choose “By Delimiter”.
Step 4: In the dialog:
- Choose the delimiter (e.g., comma).
- Choose “Each occurrence of the delimiter”.
- Click OK and save your changes.
The result will display your data split into separate columns.
Option 2: Split Cells Into Rows
In this section, we will learn how to split a cell into rows in Excel.
Step 1: Select the column with the cell values (e.g., Fruits).
Step 2: Click on “Split Column” By Delimiter.
Step 3: In the dialog:
- Choose comma as the delimiter.
- Select “Split into Rows” from the Advanced options.
- Click OK.
The result will display your data split into separate columns.
Benefits of Splitting Cells in Excel
Splitting cells in Excel can greatly enhance how your data is organized, interpreted, and used. Instead of storing multiple pieces of information in a single cell, separating the data into individual cells or columns improves both usability and accuracy.
1. Improves Data Clarity and Readability
When you split a cell that contains combined information, it becomes much easier to read and understand the data, as the separated data is more structured, and users can instantly identify what each part represents. For example, the full name of the person is Ram Charan. After splitting, “Ram” appears in one column and “Charan” in another.
Before:
Full Name
Ram Charan
After:
First Name | Last Name
Ram | Charan
2. Enables Easier Sorting and Filtering
When you split combined values in a cell (e.g., “Product Name – Category”), you separate the information into meaningful parts, like “Product Name” in one column and “Category” in another. This allows you to sort or filter by just one part of the data, rather than dealing with the entire string. For example,
Before:
"iPhone 14 - Electronics"
After:
Product Name | Category
iPhone 14 | Electronics
3. Supports More Flexible Data Analysis
When data is split into separate cells, it becomes much easier to apply formulas and functions to specific elements of that data, which improves accuracy and flexibility in data manipulation. By splitting the cells, you can create PivotTables that group or summarize by a single field like “State”, “Department”, or “Product Type”. For example,
Before:
"New York, NY"
After:
City | State
New York | NY
4. Improves Data Entry and Validation
When you keep each type of data in its own column, like separating first name, last name, age, email, etc, you make it easier to enter, review, and validate data accurately. Instead of placing mixed information into one cell, you can give structure to your data, which helps Excel to process it more reliably. It reduces manual entry errors caused by the combined values. For example,
Before:
"1236547890, [email protected]"
After:
Phone | Email
123654789 | [email protected]
5. Helps with Exporting, Importing, and Integration
When your data is split into distinct columns, it makes it easier to share, upload, or sync that data with other systems, like databases and APIs, and reporting tools expect structured fields, not combined strings. It is helpful as it avoids the need for manual reformatting when exporting or importing, and also maintains data integrity. For example,
Before:
Ram Charan, 12365495
After:
First Name | Last Name | Phone
Ram | Charan | 12365495
Common Errors While Splitting Cells in Excel
Splitting cells in Excel is useful in many ways, but one has to be careful while splitting cells in Excel. There can be many errors that can occur while performing this operation. Some of the most common issues are discussed below:
1. Trying to Split a Merged Cell: If you have merged cells, Excel will not allow you to split the content of the cell until you unmerge them. The main problem is that only the top-left part of the merged cell holds the actual data, and the others are left as blank spaces.
2. Using the Wrong Separator: When you try to split a cell, but the data does not split the way as expect. You should choose a specific character, like a comma, space, or dash, to know where to split. If you choose the wrong delimiter, you will get unexpected results.
3. Losing Leading Zeros: When you split numbers like 00123, Excel turns it into 123 because Excel treats the numbers as numbers, so it removes any “unnecessary” zeros at the front. For this, you can change the cell format to Text before splitting, or add an apostrophe before the number, e.g., ‘00123.
4. Formulas Giving Errors Like #VALUE: When you try to split a cell using a formula like LEFT, RIGHT, MID, or FIND, but Excel returns a #VALUE! error. It generally occurs when
- The delimiter does not exist in the cell.
- The FIND function fails because it is unable to locate the specified character.
- You apply the formula to a blank cell or an unexpected format.
5. Accidentally Overwriting Data: This generally happens when you use Text to Columns or a formula to split the contents of the cells, and suddenly, the data in the adjacent columns disappears. Because when you split data, Excel writes the split values into the cells to the right of the original column, and if there is existing data in those columns, Excel overwrites it without warning, which can cause data loss.
Unlock Your Future in Data Analytics
Start Your Data Analytics Journey for Free Today
Conclusion
From the above article, we learnt that splitting cells in Excel is a useful way to organize combined data into a cleaner and structured format. There is no direct feature that can be used to split the cells in Excel. But it has simple tools like Text to Columns, Fixed Width, Flash Fill, formulas, and Power Query to help you separate the content of the cell easily. These methods make it easier to sort, filter, analyze, and share your data with other tools or systems. Whether you are working with names, addresses, or product details, splitting cells in Excel can improve clarity and save you time. Just remember to check for common mistakes like overwriting nearby data or using the wrong separator.
If you want to learn more about Excel and need to know about the splitting of cells in Excel, you can visit our Master Data Analytics Course.
How to Split Cells in Excel – FAQs
Q1. What is the shortcut formula for split cells in Excel?
You can use the Formula =LEFT(A2,SEARCH(” “,A2)-1) to split the cell
Q2. How to split cells in Excel shortcut?
Use the Text to Columns feature with the keyboard shortcut: Alt + A + E
Q3. How to split cells in numbers?
You can’t split a cell in Apple Numbers the same way as in Excel. It doesn’t support Text to Columns or Power Query
Q4. How to divide in an Excel formula?
To divide in Excel, use the forward slash symbol (/) as the division operator within a formula. For example, to divide the value in cell A1 by the value in cell B1, the formula would be =A1/B1.
Q5. What is the unique formula in Excel?
The UNIQUE function in Excel extracts a list of distinct, or unique, values from a range or array, effectively removing duplicates. It is given by “=UNIQUE(array, [by_col], [exactly_once])”