Printer-friendly versionEmail

Learn how to split one field into multiple fields.

In this example, we take a field called Full Name and split it into First Name and Last Name.

Transcript

Welcome to Teaming for Technology’s tip number 50. Today, we’re working with Excel. We’re going to show you how to use the Text to Columns functionality to separate fields. Specifically, we have a list of first and last names in column E and we’d like those split into first names in column F and last names in column G.

When splitting data, the thing to keep in mind is that we need some sort of separator between pieces of data like a comma, colon or a space.
We’ll begin by selecting the column of data that we’d like to separate, so in this case, E5 through E12.

Then we’ll head up to the Data Ribbon and click on the Text to Columns button. You’ll see a Convert Text to Columns Wizard appear with Step 1 of 3 listed. This first step asks us about our data. Do we have delimited data or fixed width? The description of delimited it tells us that this includes data in which characters separate each field. In our list, a space separates each field, so we’ll choose that option. In this sort of scenario, you’ll almost always select Delimited.

Click Next to head to step 2. Here you’ll see we are able to tell Excel what separates our data. Once again, we have a space between first and last name. After you click in the space box, a vertical line appears splitting your data in the preview window. That looks like what we’re going for.

Click Next to head to the last step. In this step we have two options. The first allows us to format the data – we’ll just keep it in general format.
And the next option asks where we want the data to appear. The default is right on top of our original data. But in our case, let’s stick it one column to the right so that we can see our original data and our new split data. So enter F5 instead of E5. After you click finish, Excel will pop up a window asking if you want to replace the contents of your destination cells. In our example, those destination cells are empty, so that’s just fine.

Click ok to finish it up.

Thanks for watching T4T’s tip number 50 – how to separate fields in Excel. See you next time.