Locked lesson.

Upgrade

Quick reference

Methods for Splitting Data

Learn how to split data across cells using three methods: Flash Fill, Text to Columns, and Text Functions. 

When to use

We use this method whenever we want to take data from one cell and split it across other cells. 

Instructions

There are several methods we can use to split data across cells. 

Split Data: Flash Fill

The first method we can use is Flash Fill. 

In this example, we need to split the name and extension number in column A across the cells in columns B, C, and D. 

  • Type the first one as an example. 

  • Click anywhere in column B and press CTRL+E. 
  • Repeat this for the other columns. 

Split Data: Text to Columns

We can use the Text to Columns utility in Excel to split text across columns. Text to Columns uses the delimiter we specify to know where to split the data. 

  • Select the data in column A. 
  • From the Data tab, in the Data Tools group, click Text to Columns. 
  • Choose Delimited. 
  • Click Next.

We need to specify what separates the first name, last name, and Ext number in column A. 

  • Choose Space from the list.

  • Click Next. 
  • In the Destination field, click the cell where you want to first item to go. 
  • Click Finish. 

Split Text: Text Functions (LEFT, RIGHT, MID, FIND)

We can use Excel Text Functions to split data across columns. 

In this example, we need to separate out the First Name, Full Name, and Ext No. 

The LEFT Function

We can use the LEFT function to extract the first name from cell B7.

The LEFT function has two arguments:

text - the cell where the text is contained. 

num_chars - the number of characters from the left of the cell we want to extract. 

The formula below will extract the first 4 characters from cell B7. 

This will work for the first name in the list as it has 4 characters. However, when we copy the formula down, it doesn't work because the names are inconsistent lengths. 

Instead, we can modify the formula so it finds the first space in the name and extracts everything before. 

  • Press Enter and copy the formula down. 

We can also use the LEFT function to extract the full name from column B. We can do this by finding the first bracket and extracting everything to the left. 

The RIGHT Function

The RIGHT function is similar to LEFT except that it extracts characters from the right-hand side of the cell. 

We can use the RIGHT function to extract the extension number. As all of the extension numbers are consistent in length, we can simply extract the last 7 characters. 

  • Press Enter and copy down. 
The MID Function

The MID function helps us extract text from the middle of a cell. 

The MID function has three arguments:

text - the cell that contains the text.

start_num - the character in the string where we want to start extracting from. 

num_chars - the number of chars to extract.

In this example, we want to extract the extension number ONLY and not the 'x' or the brackets. We can use MID and FIND to do this. 

In this example, we are using the MID function to find the first occurrence of "x" and then we are extracting the next 4 characters. The "+1" ensures that the "x" is not included in the extraction. 

  • Press Enter and copy down. 

Login to download
  • 00:04 In the previous lesson, we took a look at some techniques that we can employ for
  • 00:09 combining data together into one cell.
  • 00:11 So now, what we're going to do is effectively the opposite of that,
  • 00:15 and we're going to see how we can split data up across columns.
  • 00:18 Now, the first method I'm going to show you when it comes to splitting data up is
  • 00:23 probably one of my favorite things in Excel, and that is Flash Fill.
  • 00:27 We took a look at this earlier in the course, so we're not going to linger
  • 00:31 too long on this, but it is a method you can use to split data up.
  • 00:35 For example, I have on the screen here just a table that contains some staff
  • 00:39 members, and then we have their extension number in brackets.
  • 00:43 And what I need to do is I basically need to split this information up into
  • 00:47 different columns.
  • 00:48 I need the first name, I need the full name, and
  • 00:51 I also need the extension number.
  • 00:53 So all we need to do here to use Flash Fill is just provide the pattern.
  • 00:57 So just provide an example of the first one.
  • 01:00 So that is the pattern that I want for all of these names.
  • 01:04 Notice the extension number.
  • 01:05 I've just picked out the number, I haven't included the x or the brackets.
  • 01:10 So now, to invoke Flash Fill, we just need to click in the first column Ctrl+E,
  • 01:16 Ctrl+E, Ctrl+E, and our work here is done.
  • 01:20 This is by far the simplest method you can use for breaking data up.
  • 01:24 So if we have that method, why would we need any other method?
  • 01:28 Well, again, as we spoke about in the Flash Fill tutorial,
  • 01:32 Flash Fill doesn't work in every single scenario, particularly if you have blank
  • 01:37 columns in between the data that you want to split and where you have your formula.
  • 01:42 So we do need to know some additional methods for
  • 01:45 splitting data up across different cells.
  • 01:47 So let's take a look at our next example, which is Text to Columns.
  • 01:52 And Text to Columns is one of those features that's been around in Excel for
  • 01:56 years and years.
  • 01:57 Now, Text to Columns is great, but it does have its drawbacks.
  • 02:01 So let's take a look at how it works.
  • 02:03 So what I could do here if I wanted to break this data up, and this time,
  • 02:06 I want the first name, the last name, and the extension number.
  • 02:10 So all I need to do here is select my data, jump up to the Data tab,
  • 02:15 and choose the Text to Columns button over here.
  • 02:19 Now, this is going to take us into a wizard.
  • 02:21 Now, notice here that Delimited is already selected.
  • 02:25 And what Delimited basically means is that the data that I've selected contains
  • 02:30 a delimiter, in this case, a space.
  • 02:32 That is what separates the first name from the last name from the extension.
  • 02:37 So we do have a delimited file.
  • 02:39 Let's click on Next, and then we can define what our delimiter is.
  • 02:43 So I have a space.
  • 02:46 Now, as soon as I select Space,
  • 02:48 notice the data preview shows me how it's going to break up that data.
  • 02:52 So I'm like, okay, cool, that looks good.
  • 02:54 Let's click on Next, and then I can choose my destination column.
  • 02:58 So I don't want to put it in B7, I want it to go into C7.
  • 03:03 Let's click on Finish.
  • 03:04 Now, sometimes you'll get this message, there is already data here when there
  • 03:08 actually isn't, and that can be because you've had data in these cells previously.
  • 03:13 You can just click on OK and it will override it.
  • 03:16 So now I have my data beautifully split up.
  • 03:19 However, check out the extension number column.
  • 03:22 I wasn't able to use Text to Columns to get rid of the x and the brackets.
  • 03:26 I have to include those if I'm using Text to Columns.
  • 03:30 Now, this might be good enough for you, and if so, that is absolutely fine.
  • 03:34 And you can use Text to Columns in many different scenarios.
  • 03:37 Let's take a look at the third option, we can split up text using text functions.
  • 03:43 And what I will say about these text functions is that they can get very,
  • 03:47 very complicated.
  • 03:48 So let me show you an example.
  • 03:50 Now, once again, we have similar data.
  • 03:52 I just want to break up these names and extension numbers.
  • 03:55 So what I'm going to do here, in column C,
  • 03:58 we're going to extract the first name using the LEFT function.
  • 04:03 So let's type in =LEFT.
  • 04:05 Now, the LEFT function has two arguments, text and number of characters.
  • 04:10 So what we're basically saying here is we want to extract from this cell just here,
  • 04:15 and then we need to specify the number of characters from the LEFT,
  • 04:20 what do we want to extract?
  • 04:22 So if I want to extract the first name Lucy, Lucy is four characters long.
  • 04:26 So I can type in 4, close the bracket, hit Enter, and that seems to work.
  • 04:31 But the problem occurs when we copy this down, because not each first name is
  • 04:36 the same length, not each first name has four characters.
  • 04:40 So you can see that for some of these, for example,
  • 04:44 Harry's cutting off the y, Brooke, Tricia, so on and so forth.
  • 04:48 So we need to do this in a slightly different way.
  • 04:51 So let's delete out what we have here, and let's try this again.
  • 04:54 Because if we want to extract the first names from this list,
  • 04:58 we need to go about this in a different way.
  • 05:01 We're going to use FIND.
  • 05:04 And what I'm basically going to try and do here is I'm going to find the first space
  • 05:08 and then extract everything before that, and that should get the first names.
  • 05:13 So with FIND, I'm going to say FIND the space within this text.
  • 05:21 Let's close the bracket for FIND, close the bracket for LEFT, hit Enter.
  • 05:25 And then if we double-click to copy this down, I now get exactly what I want.
  • 05:30 What about if I to extract the full name?
  • 05:33 Well, we can use LEFT to do this as well.
  • 05:36 Again, we're selecting the text in cell B7.
  • 05:39 We want to do another FIND.
  • 05:41 Now, because I want the full name, and
  • 05:44 I can't specify with these functions that I want the second space,
  • 05:48 I'm going to choose to extract everything to the left of that first bracket.
  • 05:53 So in quote marks, we're going to find the bracket within this text.
  • 05:57 And if we close off FIND and close off LEFT, let's see what we get.
  • 06:02 Well, we get pretty much what we're looking for, but
  • 06:05 it is including that bracket.
  • 06:06 So what we need to do is go back to our formula and
  • 06:09 just say that we want to minus one character.
  • 06:13 So we're going to put a -1 in there, and we get exactly what we want.
  • 06:17 Now, this time, we want to extract the extension number.
  • 06:20 Now, because the extension number is on the end,
  • 06:23 we're going to use the RIGHT function this time.
  • 06:25 So let's go for RIGHT.
  • 06:28 We're extracting from cell B7, and then we want to choose the number of
  • 06:32 characters from the right of the cell that we want to extract.
  • 06:35 Now, if I want everything here, I need to count the brackets as well, so one,
  • 06:40 two, three, four, five, six, seven characters.
  • 06:43 So if I type in 7, close the bracket and hit Enter,
  • 06:46 I pretty much get exactly what I'm looking for.
  • 06:49 Now, what if I don't want to include the x and the brackets?
  • 06:53 Well, this is where I would need to use the MID function, and
  • 06:57 mid extracts text from the middle of a string.
  • 07:00 So let's do this, again, but we're going to use MID.
  • 07:03 Our text is over here, and this time, we're going to find the x.
  • 07:11 Where you're going to find it, in cell B7.
  • 07:14 We're going to close the bracket, and then how many characters do we want to extract?
  • 07:18 Well, once we found the x, we want to extract the next four characters,
  • 07:23 so we need 4 in here.
  • 07:24 Let's close the bracket and see what we get.
  • 07:27 Well, it's almost right, but it's including the x.
  • 07:30 So we don't want it to include the x, so we basically want to plus one character.
  • 07:35 So let's click in our formula, then where we have B7,
  • 07:39 we're going to just do a +1, which is going to move it along one character and
  • 07:43 extract exactly what we're looking for.
  • 07:46 Copy down, and we are done.

Lesson notes are only available for subscribers.

Gift this course