In the last video,
we saw how Alex ran several diagnostics to identify what was
the problem with these numbers in Ivens column. And there were quite a few.
The first one we're going to tackle is the problem of
the 160 characters appearing in the cells.
Now, that's a non-printing character
and our first thoughts might be to try out the clean function.
Unfortunately, clean won't work.
If we come back and look at our ASCII character set,
clean only cleans the non-printing characters from 0 up to 31.
So, it's not going to help with other non-printing characters.
And unfortunately, it's not going to help with our 160.
Fortunately, we have another really good approach and it is the Substitute function.
The Substitute function is very much like the function equivalent of find and replace.
Given some text, if we tell it what character or
characters we wanted to find and what we want to replace them with, it will do so.
Let's first look at a really simple example.
If you have a look at the e-mail addresses,
where the surname has a space in it.
Unfortunately, it has put that space in the email address as well.
And this is not going to work.
So, we need to remove those spaces.
We can't use trim because these are not extra spaces.
This is just a single space which trim won't remove.
So instead, we're going to use substitute.
I'm going to come up to my first e-mail,
and just after the equals sign but before the data,
I'm going to type in SUB and tab to select substitute.
The first argument it wants is the text that you're going to change,
which is my data G2.
Then, I'm going to type a comma.
The next argument it wants is the text you want to find and replace.
So this is easy. We're just looking for a space.
And then, the final argument is what you want to replace it with.
And if you want to simply get rid of it,
specify you want to replace it with nothing,
Open quotes Close quotes.
Now the first e-mail,
we didn't have a problem,
but when I copied that down,
you'll see emails with the spaces have been corrected.
So, that's how substitute works.
We're now going to apply that to our events.
There is a small problem though,
typing a space is easy.
Typing a non-breaking space or 160 is not so easy.
So, how do you tell the SUBSTITUTE function to
find the character you don't know how to type?
Well, there's another function that can help us here.
It's the CHAR function C H A R function.
It's basically the opposite of the CODE function we looked at in our last video.
Given an ASCII code,
it will convert it to the character.
So all we need to type is CHAR 160,
and that will basically give us
the non-breaking space character so we don't have to worry about typing it.
Let's go and try it out.
I'm going to double click on my first event,
and after the equals but before the data,
I'm going to type SUBSTITUTE.
That's the text I want to work with,
so that's fine. Then type a comma.
Now, the old text I'm looking for is the character 160,
so I'm going to type CHAR 160 and close my bracket.
Then another comma and I want to replace those with nothing,
close my bracket and press Enter.
When I copied this calculation down,
you will see all the 160s have been removed.
The length has been reduced by one,
but in a lot of cases we still have a space so we fix the one problem,
but now we need to go and remove those spaces as well,
and to do so we're going to use the TRIM function.
So back up to the first character,
let's pop TRIM in there.
So, having substituted we're going to then remove any additional spaces,
and I'm going to double click to copy that down.
And that's now looking a lot better,
our events are of the correct length.
So we actually have length matching the number of numerical characters,
our codes are all looking like numeric codes.
So, we just have one problem left.
These numbers are still not showing as numeric.
Whenever you apply a TEXT function,
your result will be text even if the characters are numeric.
So, our last step is to make these numeric,
and for that we use the VALUE function.
Bear in mind, VALUE can only be applied to text that contains numbers.
If it contains any other text character, you'll get an error.
But all of these are numbers,
so we can now go and apply it.
So just after the equals,
we're going to type in VAL and tab.
And then of course, don't forget to close your bracket at the end.
And we can see the first one is already corrected.
And if we copy that down,
all of our events are now corrected.
Let's go and double check our pivot table is now working.
So back to our pivot.
We're going to come up to our Aanalyze tab,
and hit the refresh button and there we go.
We now have the total events for each organization.
So what we've looked at this week is an approach to cleaning data,
where we keep our original dataset in a worksheet,
and then create another output dataset which
applies a range of cleaning functions to give us a nice,
clean dataset that we can now do all our processing in.
Don't forget to try the practice challenge this week,
we shall give you more practice with these great functions.
Dr Yvonne BreyerProgram Director, Design and Experience, Global MBA
