How to Split Cells in Excel Without Breaking a Sweat

Microsoft Excel is a great tool for dealing with data. Its functions are seemingly endless and it’s easy to use interface makes this program a great option for people that aren’t very computer savvy. With a few tutorials on the basics, you can be up and running, making elaborate charts and sorting through data in Excel like a pro. Here, we’re going to cover how to split cells, which essentially means split data into different cells.

Why split cells?

In excel, the function is called “split cells” but it really means split the data that’s in the cells to be spread out amongst more than one cell. Splitting cells can be great when you’re importing data from somewhere else but you would like to organize it without spending days copy and pasting. Sure, copy and pasting is easy enough, but it’s time consuming and Excel aims to save you time by having so many awesome inclusive features. We will use the example of names in the format of first name, last name below. This is a prime example of when you would want to split cells so that you have a column titled First Name and one called Last Name.

What is a cell?

A cell, is one square on the Excel spreadsheet that you enter data in. These cells are identified by the column and the row that they are in. For example, A1, B9, G5, etc. The column always comes before the row, or, letter before number. If you’re really new to using Excel you should take a few minutes to learn the basics before getting started.

Alright, let’s go ahead and get started.

Step 1

Open a new spreadsheet by going to File–>New. If you have an existing spreadsheet with your data in it, you can open it by going to File–>Open.

Step 2

If you already have your data on the spreadsheet, skip this step.

If you are importing data from somewhere else, go ahead and do so by clicking on Data at the top of your screen. This will show you options on a ribbon right below. To the far left you should see a few options for getting external data. Select the most appropriate one for you.

Step 3

All of your data should be on your spreadsheet now. If you don’t already, add a title like Name in cell A1. So it’ll look something like this:

Step 4

Now, select the data you want to split. To do this you can either click on the column heading like A, to highlight the whole column, you can click and drag your mouse over the desired data in one column, or, you can individually click cells by holding down CTRL while selecting in one column. It doesn’t matter how you do it, as long as you end up having data highlighted. You cannot select data in multiple columns for splitting. So, you couldn’t select a cell in column A and data in column B as well. Don’t highlight the name of the column, unless you want to split that too.

Step 5

With your desired data highlighted, click on Data again from the top of your window. This will open up the same ribbon of options as when you imported data (if you imported data). You will see an option towards the right called text to columns. Click it.

Step 6

Selecting the text to columns button will prompt you with a wizard to walk you through splitting your cells. In step one, you will choose whether you want your text separated at a fixed width or delimited. A fixed width gives you complete control over your cell split. If you want to split it after 3 characters, you can, or 7 characters. So Johnny Cash would end up being Joh    nny Cash. Or, you can choose delimited which lets you select a specific character after which the data split will happen, so a comma, or a period. If your data is set up like, Last name, first name, the delimited option could be a good one to use. I’ll show you an example of both.

Delimited

If you chose delimited, you will see step two of the wizard. Here is where you can select how you wish to separate your data. Since the common factor of my data is a space, I will choose space from the check box list. Figure out what’s best for you and check the box. You will see a preview of your split data as you select the boxes. If you click Finish your data will be split just like the preview showed you. If that’s all you were looking to do, then consider yourself done!

If you want to fine tune the process a bit more, go to Next. Here you can select your destination cell, change the column format, and change the numerical data separator. The destination cell will default at $A$2, which is just cell A2. This will make your data look like:

If you decide to change the destination cell to say, B2, you can click on the destination cell box and then click on the cell you wish your data to go into. I changed it to B2 and here’s what I got:

Feel free to play around with the advanced options to get what you’re looking for. IF you try something and don’t like it, just hit CTRL+z, this is the shortcut for undo.

Fixed Width

If you decide to select fixed with, check the box, and click next.

The next screen will give you details on how to select a fixed width break. All you need to do is click in the “data preview” box and you will see an arrow appear. This arrow determines where the data will split. You can delete the arrow, as it says, by double clicking it. Once you’re happy with the position, click finish.

If you click finish, you’re done splitting the text. It will look something like this:

If you want more advanced options, like with delimited, you can select next instead of finish and choose your destination cells, change the numeric data separators and column data names.

That’s all there is to it! Isn’t that much easier than copying and pasting? If you want to learn more cool tips for making your life easier, you can in this Excel formula and function training.