Skip to main content

How to split cells in Microsoft Excel

Microsoft Excel is an incredibly powerful application that can tackle everything from simple tables to complex financial spreadsheets, and you can even password-protect an Excel file to keep your data secure. It can take years to master Excel's functionality, and most of us don't have that much time.

Related Videos

Difficulty

Moderate

Duration

30 minutes

What You Need

  • Microsoft Excel

Sometimes, you have something very simple that you need to accomplish. Splitting a cell into multiple cells is one such task, and it can be helpful when you need to clean up a spreadsheet full of data. In this guide, we'll show you how to split cells in Microsoft Excel in a few easy steps.

How to split a cell into multiple rows

Splitting a cell into multiple rows can be helpful if you need to break out data such as names and titles. In the small example spreadsheet, that's exactly what we have.

Step 1: To split these cells into their separate parts, select them and then click on Text to Columns in the Data menu.

how to split cells in excel text columns selection
Mark Coppock/Digital Trends

Step 2: In the dialog that pops up, you'll need to choose how to split your data. You can choose delimited, which splits the data based on characters embedded in it such as spaces and commas, or you can choose fixed width where your data is uniform. Here, we're using Delimited Data, so select that option and click Next. Here, we want to choose Space, because that's what separates each data element in our list. Excel will show you how your data will look in a preview at the bottom of the window.

You can choose to treat consecutive delimiters as one (such as having multiple spaces between each data element) or not, and whether to identify text via a qualifier.

how to split cells in excel choosing delimeter type
Mark Coppock/Digital Trends

Step 3: Click Next. In this window, you can choose the data format, the beginning destination cell (defaulted to the current cell), and some Advanced options such as how to recognize numeric data.

Note that the columns next to the destination cell must be empty or you'll be presented with an option to overwrite any existing data. It's best to create empty columns that equal the number of columns you'll need to display your split data.

how to split cells in excel setting other delimiter options
Mark Coppock/Digital Trends

Step 4: Click Finish. Your data will now be split into multiple columns.

how to split cells in excel data into multiple columns
Mark Coppock/Digital Trends

How to split a column in Microsoft Excel

The process for splitting an entire column in Microsoft Excel is exactly the same as for splitting specific cells.

Step 1: You first select the column, and then click on Text to Columns in the Data menu.

how to split cells in excel selecting a column
Mark Coppock/Digital Trends

Step 2: Next, simply follow the same steps as used in splitting a cell.

How to use a formula to split a cell in Microsoft Excel

When you have just two data element in cells and need to split them, there are two relatively easy formulas you can use that takes advantage of a couple of text function in Microsoft Excel. If you have more than two data elements, some complicated programming is required which is outside the scope of this guide.

Step 1: First, you'll use the formula =LEFT(A2,SEARCH(” “,A2)-1) in cell B2 in this example to pull out the first name. Replace the cell reference as required in your spreadsheet.

The LEFT text function will extract a specific number of characters from the left part of a cell. The SEARCH function will find the space that separates the data elements and tell the LEFT function how many characters to extract.

how to split cells in excel left extraction function
Mark Coppock/Digital Trends

Step 2: Copy the formula to the rest of the cells, making sure to change the cell reference number accordingly. You can drag and copy the formula.

how to split cells in excel copy left function
Mark Coppock/Digital Trends

Step 3: Next, you'll use the formula =RIGHT(A2,LEN(A2)-SEARCH(" ",A2)) to extract the right portion of the cell. Again, make sure the cell references are correct.

how to split cells in excel right extraction function
Mark Coppock/Digital Trends

Step 4: Again, copy the formula to the rest of the cells, making sure to change the cell reference number accordingly. You can drag and copy the formula.

how to split cells in excel copy right function
Mark Coppock/Digital Trends

Step 5: These formulas are dynamic, meaning if you change a value in a cell then it will reflect in the split cells. Let's change Bob to Jose in the first cell.

how to split cells in excel dynamic formulas
Mark Coppock/Digital Trends

It's as easy as that. And now that you've successfully split your cells, you can manipulate your data in more fine-tuned ways.

Editors' Recommendations

How to delete a page in Word
A person using MS Word.

Blank pages in a document can make an unprofessional impression on editors or potential employers. Microsoft Word is known to create blank pages in a document, so you should make sure to delete them whenever possible. Lucky for you, we can show you how to delete a page in Word in just a few steps.

Read more
Best Microsoft Office deals for January 2023
Students using Microsoft Office software on their laptops outside.

Mircosoft Office is so ubiquitous these days that it's practically a necessity for anyone who writes, manages spreadsheets or gives presentations for a living. Surely you've heard of Microsoft Office, even if you've never had the pleasure (if you'd call it that) of using it yourself. The Office suite has a lot more to offer than just Word, Excel, and PowerPoint, though, so if you need help finding the right package for your needs, our roundup of the best Microsoft Office deals can make things a bit easier -- and hopefully save you some cash.
Today's best Microsoft Office deals
Microsoft 365 Personal (1-year) -- $59, was $70

This license includes a 12-month subscription to Microsoft 365 Personal for a single user, allowing access to the entire Office Suite, including Word, Excel, PowerPoint, Outlook, and more. You'll also get up to 1TB of OneDrive cloud storage space -- so you can move files seamlessly between your local devices and the cloud. It's compatible with both PC and Mac. It is a great option for students, professionals working from home, and anyone that needs access to the Microsoft Office Suite from a personal computer.

Read more
Microsoft Word Free Trial: Get a month of service for free
A person using MS Word.

Windows may be a staple Microsoft product, but even as a software company it delivers far more than the popular operating system. Microsoft also dominates the enterprise software space with its Office app suite, which is relied on by millions of professionals, students, and average folks around the world every single day. One of the more renowned Office apps is Microsoft Word, a versatile and capable word processor that's essential for typing and editing written documents, among other things (and there's a lot of things you probably didn't know you could do in Microsoft Word). Microsoft Office is paid software, however, Word included, and for some, the cost may seem high even with some of the best Microsoft Office deals floating around. So, if you're wondering if there's a Microsoft Word free trial or any other way to get it for free, read on to find out.
Is there a Microsoft Word free trial?

Microsoft Word is actually part of the company's wider Office app suite. Now known simply as Microsoft 365 (formerly Microsoft Office), Microsoft's enterprise software is available in a number of different packages that are now subscription-based; the company has retired the older bundles that were available for a one-time payment. That means if you want a Microsoft Word free trial, you'll need to sign up for the Microsoft 365 trial, which includes the Word app along with Excel, PowerPoint, Outlook, Teams, OneDrive, and Editor.

Read more