split cells in excel

How to Split Cells in Excel

In my last post, we talked about how to join cells with a comma using VBA. This week, I’d like to discuss how to split cells in Excel. You might need to split one cell into multiple cells for a few reasons:

  • You downloaded a file that Excel didn’t know how to split (log files can be an example of this).
  • You have a column that you want to split into multiple columns based on a specific character.
  • …ok, I know I said a “few reasons,” but I can only think of 2 reasons why you’d like to do this. However, I’m sure you have run into other reasons for needing this. If so, please share them with us in the comments!

So let’s dig in and see how we can split cells in Excel.

Split Cells in Excel

Most of the time, you will probably need to split cells in Excel without having to use any advanced feature. The typical case is when you have a set of cells that have info in them that should be broken up by a comma, semicolon, tab, or other character.

Say you have this simple set of data:

split cells in excel - simple 1

Here we have a single cell that has 3 items in them separated by a comma.

In Excel, there is a tool we’re going to use called Text to Columns. This tool will take a columns of cells and separate them into multiple adjacent cells based on a character that you specify.

Want FREE Excel Tips?

Of course you do. We all do! Join our FREE email list and be first-in-line when new posts come out!

Click in the Data tab –> Text to Columns

text to columns

A Wizard will pop-up and help you split cells in Excel:

text to columns wizard

In this wizard, you will be guided on how to split up your cells into multiple ones. There are a lot of options you can choose from and it can get a little complicated, so let’s step through it.

Step 1 – Fixed Width or Delimited

In the first window that pops up, you will be asked if your data has a fixed width or it is delimited.

Delimited

This is a very common choice. A delimited data set means that you have data that should be split up by some character – like a comma. In our example, we’re going to split up the data with a comma.

item one,item two,item three

Fixed Width

Fixed Width means that there is a certain amount of “white space” between each column in your data set. For example, if you have a Tab between each column, our example might look like this:

item one    item two    item three

In the text above, each item is separated by 4 spaces. Since each section between each item has the same amount of spaces, Excel will understand this to be the delimiter to split up the cell into multiple cells.

For our example, we will pick Delimited. Click Next.

Step 2 – Choose your Delimiter

Text to Columns - Choose your Delimiter

The next step is to choose which character you want to split up the cell by. “Tab” is selected by default. In our example, let’s uncheck “Tab” and check “Comma” instead.

Selecting Multiple Delimiters
Notice that these are checkboxes and not radio buttons. This means that if you have multiple Delimiters selected, then the cell will be split up by as many delimiters as you specify in this list.

Check out the Data Preview section. The vertical lines indicate the new cells that will be created by splitting up the cell you selected. Click Next.

Step 3 – Formatting the new data

Text to Columns - select your delimiter

In the last step, this is where you format each column’s data. This part of the wizard has some interesting advanced settings, which we will get into in a later post. For now, we will accept the default settings. Click Finish.

Results

Text to Columns - Results

Once you hit finish, Excel will take the cell you selected and split it up into multiple columns based on how many commas it found in the cell. For this example, there were 2 commas, which resulted in 3 columns (since data was found on either side of each comma).

I know this was a very basic example of how to split cells in Excel, but I wanted to give you a quick intro on the tool that Excel provides. Do you have any specific questions on splitting up cells in Excel? Please let me know in the comments below! I’m curious to know what you’d like to learn about this feature for future posts.

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Karen Williams

    How do you remove line breaks within a cell?

    • Joseph

      Hi Karen, this can be a little tricky, but give this a shot and let me know if it works out for you:
      – In your spreadsheet, highlight the cells where you wan to find and replace line breaks
      – For this next part you will need a numeric keypad for this, so I recommend doing this on a regular desktop computer since I had so much trouble trying to do this with my laptop…
      – With the cells highlighted, press Ctrl+H. This will bring up the Find and Replace window
      – Click in the Find text box and with the NumLock turned on, hold Alt and press 010, then let go of the Alt key. This will place a new line character in the Find text box. Note: there is also another special character that might be in your cells causing the line break, which is Alt+013.
      – In the Replace text, make sure it’s empty
      – Click Replace All

      This only works if you used Alt+Enter inside of your cells to create the line break. However, if you used a formula to enter a new line break, then do a Find & Replace on the following:
      “&CHAR(10)&”
      or “&CHAR(13)&”

      And replace with blank. This will remove the piece of the formula causing the line break. For example:
      =”Test”&CHAR(10)&”New Line”

      would be replaced with

      =”TestNew Line”

      I think I’ll make this another post now that I write all this out 🙂 I hope this helps!
      -Joseph