How to Split a Cell in Excel

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?
• Split Cells in Excel Using a Delimiter
• Split Cells in Excel Using the Fixed Width Option
• Split Cells In Excel Using Flash Fill
• Split Cells in Excel Using Formulas
• Split Cells Into Columns or Rows Using Power Query
  • Option 1: Split Cells Into Columns
  • Option 2: Split Cells Into Rows
• Benefits of Splitting Cells in Excel
• Common Errors While Splitting Cells in Excel
• Conclusion

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

Explore Program

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

Split Cells in Excel Using Formulas

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.

1. Extract the First Name

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.

2. Extract the Last Name

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 rows.

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

Explore Program

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