Split Cells

Why Split Cells in Excel?

Splitting cells is vital for better data organization and analysis. It breaks down combined data into separate parts, simplifying filtering, sorting, and analysis. Whether it’s names, addresses, dates, or structured info, cell splitting enhances data clarity and usability.

Scenarios Where Cell Splitting Is Useful

  • Creating employee timetables
  • Full Names: Splitting full names into first and last names for personalized communication and sorting.
  • Addresses: Dividing addresses into street, city, state, and zip code for efficient mapping and analysis.
  • Dates and Times: Separating date and time information into individual cells for accurate calculations.
  • Keywords or Tags: Extracting individual keywords or tags from a single cell for better categorization.

You might also want to check out this article on the Top 20 Common Problems in Excel! Don’t forget to take advantage of this Free Microsoft Excel Online Course – 20+ Hours Beginner to Advanced Course!

Download the Excel Workbook below to follow along and understand how to Split Cells in Excel –


Now, let’s explore each of these steps individually!

Splitting a Single Cell

To perform cell splitting in Excel, introduce a fresh column, modify column widths, and then combine cells as needed.

For this example, we want to create an employee timetable. Susan Forst and Aaron Torres start their shift at 8:00AM and 10:00AM, respectively. David Seed, however, starts his shift at 8:30AM. How do we showcase this on our spreadsheet?
Split Cells in Excel

Select Column C.

Right click, then select Insert.
Split Cells in Excel

By default, column width is set at 64 pixels. Adjust the width of columns B and C to 32 pixels.
Split Cells in Excel

Select B1 and C1.

Navigate to the Home tab, locate the Alignment group, and select the downward arrow adjacent to Merge & Center. Proceed to choose the option labeled Merge Cells.
Split Cells in Excel

Merge cells B2 and C2, and B4 and C4, as well.

Alter the background color of cell B3 to No Fill.
Split Cells in Excel

Voila! The employee timetable will now show the 30 minute increment.
Split Cells in Excel

Text to Column

Using Excel’s Text to Columns feature to split cells is a handy method to divide data within a cell into multiple columns using a delimiter like a comma, space, or tab. Here’s how:

Start by selecting the range of cells that you want to split. This can be a single column or a range of columns.

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Go to the Data tab in the Excel ribbon. Click Text to Column.

Split Cells in Excel

In the Convert Text to Columns Wizard, you’ll have the option to choose between “Delimited” and “Fixed width.” Since you want to split cells based on a specific delimiter, choose Delimited and click Next.

Choose the delimiter that separates the data within your cells. Common options include comma, tab, semicolon, space, etc. You can also select multiple delimiters if needed. In our case, we will select Space.

You’ll be prompted to select where you want the split data to be placed. You can choose to replace the original data or specify a new location (either a new column or a different sheet). In our case, let’s specify $B$2 as the location.

Split Cells in Excel

The preview section at the bottom of the wizard will show you how your data will be split based on your chosen delimiter. If the preview looks correct, click the “Finish” button.

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Text Functions

This method is useful when you want to split a cell’s content using specific rules or conditions.

  • For the first name: =LEFT(A1, FIND(” “, A1) – 1)

Split Cells in Excel

  • For the last name: =RIGHT(A1, LEN(A1) – FIND(” “, A1))

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Select the range B2 and C2, drag from the bottom right hand corner to apply the formulas to the rest of the employees.

Split Cells in Excel

These formulas locate the space character’s position and extract the relevant part of the text. Your choice of method should match your data and needs. Text to Columns is best for basic delimiter-based splits, while text functions offer more control for intricate divisions.

Flash Fill

Suppose you want to split data into separate columns for first names, last names, and their respective departments.

Enter the full names in a column (e.g., Column A).

In an adjacent column (e.g., Column B), start typing the pattern you want Excel to recognize for splitting. In cell B2, type “Susan.” In cell C2, type the last name “Forst.” In cell D2, type “Accounting.”

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Highlight cell B2, and press CTRL + E (Flash Fill shortcut).

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Highlight cell C2, and press CTRL + E (Flash Fill shortcut).

How to Split Cells in Excel - The Easy Way | MyExcelOnline

Highlight cell D2, and press CTRL + E (Flash Fill shortcut).

Split Cells in Excel

Excel will attempt to recognize the pattern and fill in the rest of the column automatically.

Below you will find 120+ Excel formulas & functions examples for key formulas & functions like XLOOKUP, VLOOKUP, INDEX, MATCH, IF, SUMPRODUCT, AVERAGE, SUBTOTAL, OFFSET, LOOKUP, ROUND, COUNT, SUMIFS, ARRAY, FIND, TEXT, and many more.  Let us start learning for free!


Click here to access these FREE Excel courses!


If you like this Excel tip, please share itEmail this to someone


Pin on Pinterest


Share on Facebook


Tweet about this on Twitter


Share on LinkedIn