Original subtitles

In this lesson, we're going to continue cleaning Excel spreadsheets, and I'm going to show you how

you can use text functions to help you fix some of the errors that we have in Column B now if we take

a closer look at Column B, the product column.

What do you think is wrong with this column?

One.

If you said the text inside this column is inconsistent.

You would be correct if you noticed that some of the words in this column are in uppercase.

Some of them are in lowercase, such as Royal Oak in Cell B eight, and some of them are in what we

call proper case and proper case just means that the first letters of each word are capitalized.

Now, proper case is how I want everything to look in this column.

But what I don't want to do is have to go through each and every row changing all of these words to

proper case.

Something else that's wrong in this column is that I can see I have some weird spacing and this is something

which can easily be missed.

I think it's just because I've spent many years staring at Excel spreadsheets.

I have a really keen eye as to how far away from the edge of the cell text should be, and I can see

that in Column B. If we take a look at the word Kensington, the gap between the first letter and the

edge of the cell is actually a bit wider than it normally is.

If you compare that to column a Japan is a lot closer than Kensington is.

So I know that I have some kind of space at the start of this cell.

I can also see that for some of these, I have double spaces in between the words also, and it might

be that I have training spaces after these words that I can't even see.

Now, why is that a problem?

Well, again, if I was going to put this information into some kind of pivot table, or even if I was

to use a filter, it could mean that that is listed twice in the filter as opposed to once.

It also could throw up some unexpected results when we're putting together our pivot table.

So what I'm trying to say here is it's always best to make your data consistent across your spreadsheet.

So we're going to spend this lesson tidying up Column B using text functions.

Now, before we start working on this column, what am I actually going to do?

Just to make this super easy to understand is I'm going to take this data out of this table because

this table was automatically created when I imported this text file.

Now, if you want to remove data from a table or you need to do is jump up to table design.

And in the tools group, select convert to range.

Click on OK.

And now the data is no longer in a table.

Notice that when I'm collecting this data, I no longer get the table design contextual ribbon.

So now I just have bog standard data.

I'm going to insert a new column and I'm going to use the keyboard shortcut control shift plus.

Now this is what I'm going to call for now my helper column, and I'm going to use this column to tidy

up the text that we have in Column B. So the first thing that I want to do here is I want to change

everything to proper case.

So the function that we need to use for that is, as you might expect, a function called prop..

And this is one of the text functions in Excel.

There's only one argument for this particular function.

So that is the text that we want to change to proper case B to control, enter, to stay in the same

cell, and then I can double click to copy that down.

And like magic, all of my text is now consistent.

He's also worth noting that if you wanted to change everything to uppercase, you could replace proper

with upper.

And if you wanted to change everything to lowercase, you guessed it, you can just replace it with

lower.

So three really helpful little functions just that.

Now it's changed everything to proper case.

But what it hasn't done is remove these weird spacing issues that I have going on.

Now, if you want to remove what we call erroneous spaces from a text string, we use the trim command.

So what I'm going to do?

Control, shift down.

It's delete everything out.

And let's take a look at the trim function on its own.

So equals trim.

Again, it just requires the text that we want to trim.

Now, can you see the difference there?

If I drag this down a few Kensington now that we've trimmed those spaces is a lot closer to the edge

of the cell boundary than it is over here.

So those spaces at the beginning have been removed simply by using that trim function.

It's also worth noting that there is another text function called clean, which will remove things like

manual line breaks and non printing characters.

So, for example, if I had a line break in between these two words, let's put in a manual line break

out.

Enter, if I wanted to remove that line break.

I would use the clean function again.

We just have one argument let's say enter.

And we should notice this when we go past Royal Oak.

And yes, now you can see the Royal Oak is all on the same line.

It's removed that line break.

Now I'm just going to undo a few just to get rid of that line break.

So we have three functions that proper trim and clean.

Now I want to apply all three to call him B. So what I can do is I can combine them all together.

We're going to start with proper.

We're going to open a bracket.

We're going to go straight into trim.

We're going to open a bracket.

We're going to go straight into clean and open a bracket.

We then just need to select the text that we want to apply all of this to.

And then we need to close off as many brackets as we've opened.

So close of clean close of trim, close of proper hit enter.

And then I can double click to copy that down.

And I've applied three text cleaning functions all in one go.

And it looks absolutely beautiful.

So now I have a clean column, column C. And I have what I'm going to label for want of a better term,

a dirty column in Column B..

Now you might think that you can just go in here and right click and delete Column B. But if you do

that, you're going to get these reference errors because we have formulas in Column C that refer to

Column B..

So if we delete that column, it effectively breaks that formula.

So how can we keep the text that we have in here but just remove those underlying formulas?

Well, what we can do is we can use paste special to help us do that.

So what I'm going to do is I'm going to select Column C. The column that contains the formulas control

C to copy.

I'm going to go to the home tab, go to paste and I'm going to say paste values and I'm going to paste

just the cell values directly over the top.

And what that does is it just keeps the text that you can see and it throws away the underlying formulas.

So now that I've done that, I can simply delete the dirty column and I'm not going to get any errors.

I need to change this column heading as well.

So this needs to be product again, but that is a nice, clean column.

So I would.

Why that type of thing to any column that I can see has issues, for example.

I think all of these are pretty much OK.

Apart from this month name column, I can see that that has what we call erroneous spaces at the beginning

there as well.

So I'm going to do the same thing that's insert a column.

This time I'm just going to do a trim.

Let's select the text control enter and then I can double click to copy down.

I want to throw away the underlying formulas that control C to copy.

Paste and paste values escape, and then I can simply move.

This heading across and then delete Colin Powell, and whilst we're here, I'm going to apply some no

formatting, two columns which don't have it.

So I can see here that manufacturing price, sales price, all of this to here needs to have accounting

formats applied.

So control shift down Arrow to select all of those cells and let's click on accounting.

And there we go.

So we're now ready to move on to our next stage of cleaning.