Download Excel Workbook
Watch our free training video on How to Split Cells in Excel:
Table of Contents
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
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?
Select Column C.
Right click, then select Insert.
By default, column width is set at 64 pixels. Adjust the width of columns B and C to 32 pixels.
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.
Merge cells B2 and C2, and B4 and C4, as well.
Alter the background color of cell B3 to No Fill.
Voila! The employee timetable will now show the 30 minute increment.
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.
Go to the Data tab in the Excel ribbon. Click Text to Column.
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.
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.
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)
- For the last name: =RIGHT(A1, LEN(A1) – FIND(” “, A1))
Select the range B2 and C2, drag from the bottom right hand corner to apply the formulas to the rest of the employees.
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.”
Highlight cell B2, and press CTRL + E (Flash Fill shortcut).
Highlight cell C2, and press CTRL + E (Flash Fill shortcut).
Highlight cell D2, and press CTRL + E (Flash Fill shortcut).
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!
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.
