Pinterest Pixel

How to Split Cells in Excel – The Easy Way

Why Split Cells in Excel? Splitting cells is vital for better data organization and analysis. It breaks... read more

Download Excel Workbook
John Michaloudis
Posted on

Steps To Follow

Overview

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

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 –
download excel workbook Split-Cells-1.xlsx

See also  Quick Excel Tip: Calculate Standard Error in Excel Like a Pro!

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?
How to Split Cells in Excel - The Easy Way

Select Column C.

Right click, then select Insert.
How to Split Cells in Excel - The Easy Way

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

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.
How to Split Cells in Excel - The Easy Way

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

Alter the background color of cell B3 to No Fill.
How to Split Cells in Excel - The Easy Way

Voila! The employee timetable will now show the 30 minute increment.
How to Split Cells in Excel - The Easy Way

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:

See also  Fix Incorrect Formatting Using Flash Fill In Excel

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

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

How to Split Cells in Excel - The Easy Way

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.

How to Split Cells in Excel - The Easy Way

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

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)
See also  How to Separate Names in Microsoft Excel Easily

How to Split Cells in Excel - The Easy Way

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

How to Split Cells in Excel - The Easy Way

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

How to Split Cells in Excel - The Easy Way

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

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

How to Split Cells in Excel - The Easy Way

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

See also  Extract Numbers Using Flash Fill In Excel

How to Split Cells in Excel - The Easy Way

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

How to Split Cells in Excel - The Easy Way

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!

If you like this Excel tip, please share it
How to Split Cells in Excel - The Easy Way | MyExcelOnline How to Split Cells in Excel - The Easy Way | MyExcelOnline
Founder & Chief Inspirational Officer at MyExcelOnline.com

John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship Academy Online Course.

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...