All language subtitles for 5. Splitting Data using Text Functions

In this lesson, I'm going to show you how you can split up data using text functions.

And if we jump across to the Formulas tab and take a look in the text group in our functions library,

this is where are you going to find all of the commands that are going to be using?

And you can see we have so many different text functions in here.

Now, I will say that for certain pieces of text, using text functions is a pretty easy endeavor,

but sometimes it can get extremely complicated and I'm going to show you how complex it can get at the

end of this lesson.

And then in the next lesson, I'm going to show you the way that I split up my data all the time because

it is so much easier.

But it's good to have this basic knowledge of text functions because you will find yourself using them

from time to time, particularly when combined with other functions.

So let's take a look at some of them and how they work.

Now, in this first example, I have just a small list of part numbers, and maybe I want to split these

up.

So I have the first three characters in the code column the middle, three characters in the identifier

column and the last three characters in the part column.

Now I could use text two columns to do this, but we're not going to do that.

We're going to use text functions.

So the first thing I want to extract from this string is the code, and the code is the first three

characters of this string.

So the function I'm going to use here is equals left because we're going to work from the left hand

side of the cell.

We have two arguments for this function.

The first argument is text.

So that is the cell that contains the text we want to extract.

And this is a simple case of entering in how many characters we want to extract from the left.

So because all of these are consistent, I want to extract three characters close the bracket, enter

and I can simply double click to copy that down.

Because all of these part numbers are very consistent in their format.

They all have three characters three characters, three characters.

Now, if I want to extract the middle three characters for this, I would use another text function

called Mitt, and we have an additional argument here.

So our first argument is text.

So that is the cell.

We then want the start number.

So I need to count to cross until I get to the first character that I want to extract.

So one two three four, I want to start from the fifth character and then I want to extract the next

three characters.

Close the bracket control answer and I can double click to copy that down, and I've extracted the middle

part of this string.

And then the final one.

I want to extract the last three characters.

For this, we use the write function.

Once again, we select the text string and then the number of characters from the right that we want

to extract.

So three, in this case control enter and then double click to copy down.

Now, extracting text in this way is all well and good.

And if you have very consistent numbers or strings, then this works really well.

It's super simple to put together, but more often than not you have data that isn't consistent.

So let me show you an example.

And again, if we take a look at this data down here, maybe this is something that we've imported.

We basically have four pieces of information combined into one cell and separated by a comma.

So if I want to extract the employee name from each of the cells, I'm not going to be able to do it

using the left function.

Because if this works for the first one, so let's say left, I want to extract from this cell number

of characters.

Well, for this first one, if I want to extract the entire name, that is one two three four five six

seven eight nine nine characters.

We need to count the space as a character so I could type in nine.

Close the bracket answer and once that works for the first one.

When I copy this down, it doesn't work for the others because these are not all consistent in their

length.

And this is where using text functions becomes a lot more complex.

So let me show you how I could extract the name from this cell.

We need to do something a little bit different.

So what I'm going to do here is we are going to use left, but we're going to go about this in a different

way.

Now we're going to specify the cell A12.

But when it comes to the number of characters, we need to think about this in a different way.

I can't just count across to extract the number of characters.

So what I'm going to do is look at this.

Data and think to myself, is there any common identifier that I could use here?

Well, I know that after every single name, we have a comma.

So maybe I could find the comma in this string and then extract everything before it.

And that would give me the name.

So the first thing I need to do here is I need to find that first delimiter, that first comma.

So I'm going to use the find command, another text function.

Now we find we have a few different arguments.

It's asking us which text we want to find.

Well, I want to find a comma now because this is a comma.

It's essentially text.

It needs to go in quote marks.

So I'm going to say, look for this comma.

If you had something else, so maybe you've got a space in there, you could type in a space and put

that between quote marks.

It all depends how your fields are separated.

I'm looking for a comma.

Where am I looking for that comma?

Well, I'm looking for it in cell A-12.

So let's close that off, and let's also close off our left function.

Now, if I was to hit, enter just here, I'm going to get that.

And if I copy it down.

Notice that it has extracted the name, but it's also giving me a comma on the end.

So what I can do to get rid of that comma double click to edit the formula is just on the end here between

these two brackets.

I'm going to say minus one to effectively go back to character, and that's going to get rid of that

comma.

So that formula just there, if you look at it in the formula bar, it isn't too bad.

We start with left.

We then specify the cell and we then just say, find the comma in cell.

A-12 extract a minus one.

So we're not including the comma in that extract.

So that formula isn't too bad.

Where things start to get really complicated is if I then want to extract the job title because that's

in the middle of this data.

Now the formula that you need to put together in order to extract the job title using text functions

is extremely long, very complicated and very much outside the scope of this intermediate course.

But just so you can see how long this formula is.

I've got it over here.

So if I click on this and we take a look at that in the formula bar, would you take a look at that?

We have so many different things going on there.

It's a very long and complicated formula.

It does work.

You can see it has extracted that job title.

But for me, trying to remember this and taking the time to type all of this into a cell is never the

method that I use if I want to extract the job title.

And in the next lesson, I'm going to show you by far the easiest way to split up your data.

So I'm going to jump across there now, and I look forward to you joining me.