Open In App

How to Split Cells in Excel: In 5 Easy Steps

Improve
Improve
Like Article
Like
Save
Share
Report

Microsoft Excel is a powerful data tool used in various industries. Organizing data in separate cells is crucial for clarity and analysis. We often receive data with many points in one cell due to different initial purposes.

In these situations, we frequently divide a cell into its component pieces. In this article, we will learn how to split cells in Excel using formulas, shortcut Keys, and more.

How-to-Split-Cells-in-Excel

How to Spilt Cells in Excel

What are Split Cells in Excel?

Splitting cells in Excel is essential for dividing data within a single cell into separate cells. It allows you to extract and organize information in a structured way, which makes it easier to analyze and present the data.

Most often, we need to split cells for text processing [E.g. Split Last Name and First Name from Customer name field].  

In the below data set, Column A has both Products and categories combined with a space delimiter

Dataset

Different Ways to Split Cells in Excel

Some of the different ways to split cells in Excel are listed below:

How to Split Cells Using Text to Columns Wizard in Excel

Step 1: Select the entire data set [Range A1 – A11]

Step 2: Go to “Data” >> Click “Text to Columns”, to pop up Convert Text to Columns Wizard

Text to column

Step 3: Select “Delimited” and Press “Next

convert text to column wizard

Step 4: Check “Space” and Press “Next

convert text to column wizard

Step 5: Change Destination to $B$1 cell and Press “Finish

convert text to column wizard

Output: We have achieved split cells in Excel.

output

How to Split Cells Using Excel Formulas in Excel

Step 1: Type Header text “Product” and “Category” to cells B1 and C1 respectively

Step 2: Write the below formula in cell B2 to split the first word (Product)

=LEFT(A2,FIND(” “,A2)-1)

Dataset

Step 3: Fill the formula in cells B2:B11 

Fill the Formula

Step 4: Write the below formula in cell C2 to split the last word (Category)

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

Write the Formula

Step 5: Fill the formula in cells C2:C11

Fill the Formula

How to Split Cells Using Flash Fill in Excel

Excel cell splitting using basic patterns is fairly simple to utilize. It can run in two possible ways:

  • In the background
  • Triggered manually by the user

Background execution

In this, Excel automatically offers ideas after trying to identify trends in the text.

1. Type the text element we want to extract in the first row of the first column (with the source cells on the left). Enter the portion of the text from the second cell that we wish to extract. Flash Fill now takes action and offers suggestions.

Flash Fill.

2. To accept the suggested fill, press the Tab or Enter key

Suggested fills by the Flash Fill

Manual Execution

Now that we’ve seen how the Flash fill auto-suggests the data, let’s have a look at the manual approach as well.

1. In any single row, type the text element that has to be extracted. In this particular example (Last name)

Manual execution of flash fill

2. Click Home Fill (dropdown) > Flash Fill, and choose the cells in the range that should have values entered.

Manual execution of flash fill

How to Split Cells Using Power Query in Excel

Excel’s Power Query feature can also be used to divide several cells. It has been natively available since Excel 2016.

Let’s start off with adding our data cells into the Power Query editor. The steps to take are listed below,

Step 1. Click Data tab > From Table/Range after choosing any cell from the data set

Data tab

Step 2. Make sure the “My table has headers” option is checked, and the whole range is selected. Then press OK.

Create Table dialogue box

Step 3. All the data will be visible when the Power Query editor opens

Step 4. Select Home > Split Column (drop-down) > By Delimiter from the Power Query ribbon

selecting split column by delimiter

Step 5. The Space character and At each occurrence of the delimiter are both suitable choices for our circumstances. Select OK

Split by Delimiter dialogue box

Step 6. The Employee name column is now divided into two distinct columns in the data preview pane. To change the headings to First name and Last name, double-click the header and make changes respectively

Employee name column divided into two distinct columns

How to Split Cells in Excel Using Text Functions in Excel

There are many Text Functions in Excel, but we don’t need all of them here. A few of them are mentioned below:

  • LEN- Returns the length of a String.
  • RIGHT– Extract a specified number of characters from a String’s right end.
  • LEFT- Extract a specified number of characters from a String’s left end.
  • FIND– Look for a string inside another string.
  • SEARCH– Return the positions of a string inside another string.
  • MID– Extract a specified number of characters from a String’s center.

You can use any combination of the above text Functions to achieve your split.

Enter the Formula =LEFT(A2,SEARCH(" ",A2)-1) in cell B2.

The SEARCH looks for any space in the customer name and returns its position in the string. Then, the LEFT function extracts the part of the String from the left side, up to the position returned by the SEARCH Function.

Conclusion

In this guide, I have explained how to split cells in Excel in a step-by-step manner with 4 different approaches to split cells with detailed examples. It is a very effective method for formatting the cells quickly.

How to Split Cells in Excel – FAQs

What is “Split Cells” in Excel?

Splitting cells in Excel refer to dividing the content of a single cell into multiple cells, based on specified criteria or delimiters. This process helps to organize data into separate cells, making it easier to analyze the report.

How do I split a cell into 5 in Excel?

Select your cells > Go to Convert to Text Column Wizard > Select Deliminated Option > Adjust Settings > Input Formatting Information.

How to split Cells in Excel using the Text to columns Feature?

To split cells using the Text to columns feature, follow the below steps:

Step 1: Select the cells you want to split.

Step 2: Go to the “Data” Tab on the Excel ribbon.

Step 3: Click on “Text to columns” in the Data Tools Group.

Step 4: Now choose the appropriate data type and click “Next”.

Step 5: Select the delimiter that separates the data within the cells and Click “Next”.

Step 6: Preview the data in the “Data Preview” section and click “Finish” to split the cells.

How to split cells in Excel using the fixed width option in Excel

  1. Select your cells to split.
  2. Click the “Data” tab
  3. Choose “Fixed Width”
  4. Indicate where you want to break your cell
  5. Pick a format option

How to split multiple lines in a cell into multiple rows in Excel

  • Step 1: Select the cell you want to split
  • Step 2: Open the Power Query feature
  • Step 3: Select “Split Columns”
  • Step 4: Adjust your settings

Why do we split cells in Excel?

Readability: Splitting information into smaller chunks improves scanability.

Reformatting: Divide copied content into columns to categorize it.

Analysis: Isolating parts of columns aids in focusing on individual data sets.

What is the shortcut key for split cells in Excel?

ALT + A + E 



Last Updated : 20 Mar, 2024
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads