Don’t forget to share this post

Microsoft Excel is a great tool for working with data of all types, including text. You can import or paste data from just about anywhere in just about any format, which can lead to the need to split cell contents. For instance, you may have tables where an entire record - with field delimiters - is contained in a single cell. Perhaps you have cells with the full names of customers, but you need to split the first and last names. Regardless of the reason, Microsoft Excel has multiple solutions.

In this guide, you will learn six different methods of splitting cells in Microsoft Excel. First, you will learn how to use the text-to-columns wizard to specify exactly how you want the contents split. Second, you will learn to use the flash fill tool to split cell contents based on patterns. Third, you will learn how to use a built-in text function so the split cells will remain updated: TEXTSPLIT. Fourth, you will learn how to use Power Query to split cells and how to split them into rows instead of columns. Finally, you will also learn how to split merged cells.

Split Cells Using Text-to-Columns

If you need to split the text in a cell into several cells, you can use the text-to-columns tool. The wizard will guide you through the process, and you can specify whether the contents are separated by a specific delimiter or have a fixed width.

Follow the steps below to use the text-to-columns tool to split full names into first and last names.

  1. 1. Select the cells containing the full names. On the Data tab, click the ‘Text to columns’ button.
How to Split Cells in Excel 6 Easy Methods Text to Columns
How to Split Cells in Excel? 6 Easy Methods - Text to Columns
  1. 2. In this case, the names are separated by a space, so you should choose ‘Delimited’ and click ‘Next’.
How to Split Cells in Excel 6 Easy Methods Delimited
How to Split Cells in Excel? 6 Easy Methods - Delimited
  1. 3. Select the delimiter from those available or choose ‘Other’ and type it into the text box. Click ‘Next’ to specify the destination and data types. If you want to replace the existing cells, click ‘Finish’.
How to Split Cells in Excel 6 Easy Methods Choose Delimiter
How to Split Cells in Excel? 6 Easy Methods - Choose Delimiter
  1. 4. Select the destination cell and click ‘Finish’.
How to Split Cells in Excel 6 Easy Methods Choose Destination
How to Split Cells in Excel? 6 Easy Methods - Choose Destination
  1. 5. The first and last names are now in separate cells.
How to Split Cells in Excel 6 Easy Methods First Last Names
How to Split Cells in Excel? 6 Easy Methods - First & Last Names
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

Split Cells Using Flash Fill

If you need to split the contents of a cell, you can use the Flash Fill tool. In fact, you can also use it to extract part of the contents. For simple patterns, providing a single sample cell is enough for Microsoft Excel to recognize the pattern, while more complex patterns may need two or three.

  1. 1. Open the spreadsheet containing the cells you want to spit. In the column to the right, reproduce the contents of the corresponding cell to the left up to the point where you want to split.
How to Split Cells in Excel 6 Easy Methods Type First Name
How to Split Cells in Excel? 6 Easy Methods - Type First Name
  1. 2. Grab the fill handle and drag it down to the last cell. Click the button in the bottom right corner and select ‘Flash fill’.
How to Split Cells in Excel 6 Easy Methods Select Flash Fill
How to Split Cells in Excel? 6 Easy Methods - Select Flash Fill
  1. 3. As you can see, Excel has interpreted the pattern correctly. Repeat steps 1 and 2 for the last name.
How to Split Cells in Excel 6 Easy Methods Pattern Recognized
How to Split Cells in Excel? 6 Easy Methods - Pattern Recognized

Split Cells Using TEXTSPLIT

In the examples below, you will learn how to use the TEXTSPLIT function to split the contents of a cell. One of the benefits of using formulas is that the split cells will update when the original data changes.

Follow the steps below to split a full address delimited by commas into separate cells.

  1. 1. Type the TEXTSPLIT function into an empty cell.
How to Split Cells in Excel 6 Easy Methods TEXTSPLIT Function
How to Split Cells in Excel? 6 Easy Methods - TEXTSPLIT Function
  1. 2. Select the cell with the text and add a semicolon.
How to Split Cells in Excel 6 Easy Methods Select Cell
How to Split Cells in Excel? 6 Easy Methods - Select Cell
  1. 3. Type the delimiter between quotation marks. In this case, the text is delimited by commas.
How to Split Cells in Excel 6 Easy Methods Add Delimiter
How to Split Cells in Excel? 6 Easy Methods - Add Delimiter
  1. 4. Close the parenthesis and press ‘Enter’. Drag the fill handle to copy the formula to the last row.
How to Split Cells in Excel 6 Easy Methods Split Cells
How to Split Cells in Excel? 6 Easy Methods - Split Cells
How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Split Cells Into Columns or Rows Using Power Query

Power Query is a very versatile tool that can be used to manipulate data in any way you want. This includes splitting the contents of cells into separate columns or even separate rows. Follow the steps below to split cell contents using Power Query.

  1. 1. Select the cells you want to split and go to the Data tab to access Power Query. If you’re using a Mac, copy the table and launch Power Query, as shown below.
How to Split Cells in Excel 6 Easy Methods Copy Table Launch Power Query
How to Split Cells in Excel? 6 Easy Methods - Copy Table & Launch Power Query
  1. 2. Click ‘Enter data’.
How to Split Cells in Excel 6 Easy Methods Enter Data
How to Split Cells in Excel? 6 Easy Methods - Enter Data
  1. 3. Paste the table contents using the Cmd + v shortcut. Click ‘OK’ to move to the next window.
How to Split Cells in Excel 6 Easy Methods Paste Table
How to Split Cells in Excel? 6 Easy Methods - Paste Table
  1. 4. Click ‘Transform’, then ‘Split column’ to see the options. In this case, the text is separated by delimiters.
How to Split Cells in Excel 6 Easy Methods Transform Split Column
How to Split Cells in Excel? 6 Easy Methods - Transform & Split Column
  1. 5. If you want to split the contents to separate columns, select the separator and click ‘OK’.
How to Split Cells in Excel 6 Easy Methods Split To Columns by Comma
How to Split Cells in Excel? 6 Easy Methods - Split To Columns by Comma
  1. 6. To split the contents into separate rows, select ‘Advanced’ and under ‘Split into’, select ‘Rows’. Click ‘OK’ to apply.
How to Split Cells in Excel 6 Easy Methods Split to Rows by Comma
How to Split Cells in Excel? 6 Easy Methods - Split to Rows by Comma
  1. 7. In this case, the text has been split into columns, which you can rename by double-clicking on the headers.
How to Split Cells in Excel 6 Easy Methods Rename Columns
How to Split Cells in Excel? 6 Easy Methods - Rename Columns
  1. 8. Finally, click ‘Close & load’ to send the new table back to Excel.
How to Split Cells in Excel 6 Easy Methods Close Load
How to Split Cells in Excel? 6 Easy Methods - Close & Load

Split Cell Contents to Rows Using Functions

Using Power Query is not the only way to split cell contents into rows; you can combine TRANSPOSE and TEXTSPLIT to achieve the same result.

  1. 1. Wrap the TRANSPOSE function around the TEXTSPLIT function, as shown below.
How to Split Cells in Excel 6 Easy Methods TRANSPOSE TEXTSPLIT
How to Split Cells in Excel? 6 Easy Methods - TRANSPOSE & TEXTSPLIT
  1. 2. Press ‘Enter’ to see the results. However, you won’t be able to drag the formula as usual since the results of the formula need to spill over 4 cells.
How to Split Cells in Excel 6 Easy Methods Cell Split Into Rows
How to Split Cells in Excel? 6 Easy Methods - Cell Split Into Rows

Split Merged Cells

To split merged cells, select them and click the toolbar button with the merge options. Select ‘Unmerge cells’.

How to Split Cells in Excel 6 Easy Methods Merge Options
How to Split Cells in Excel? 6 Easy Methods - Merge Options

The merged cell has been split, and the contents transferred to the cell on the left.

How to Split Cells in Excel 6 Easy Methods Unmerged Cells
How to Split Cells in Excel? 6 Easy Methods - Unmerged Cells

Want to Boost Your Team's Productivity and Efficiency?

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

  • Centralized Knowledge: Access your team’s collective wisdom with ease.
  • Collaborative Workspace: Foster engagement with flexible project tools.
  • Seamless Communication: Connect your entire organization effortlessly.
  • Preserve Ideas: Capture insights without losing them in chats or notifications.
  • Comprehensive Platform: Manage all content in one organized location.
  • Open Teamwork: Empower employees to contribute, share, and grow.
  • Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

Conclusion

You have a lot of options when it comes to splitting cells in Microsoft Excel. You now know how to split cell contents using the text-to-columns tool, where you can specify the delimiters in the original text and specify the data types in the new split cells. You also know how to use the flash fill tool to split cell contents based on a pattern. If you need to keep the split cells linked so that they are updated with changes, you know how to use built-in functions.

Depending on your version of Microsoft Excel, you can use the TEXTSPLIT function directly or combine other text functions, such as SEARCH, LEN, RIGHT, and LEFT. However, Power Query offers the most flexibility when it comes to splitting cells, as well as keeping the data connected. Finally, you also know how to split cell contents into rows instead of columns and how to split merged cells.

To learn more about working with cells in Microsoft Excel or Google Sheets, take a look at the articles below.

  • How to Combine Multiple Excel Columns Into One
  • How to Merge Cells in Google Sheets (Complete Guide)
  • How to Lock Cells in Excel (Cells, Sheets & Formulas)
  • How to Unlock Cells in Excel
  • How to Lock Cells in Google Sheets (Rows/Columns/Sheets)
Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Mar 20 2023, Updated Mar 22 2023