Practice Video: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)

Loading...

來自 Macquarie University 的課程

Excel Skills for Business: Advanced

142 個評分

Macquarie University

Excel Skills for Business: Advanced

142 個評分

課程 4（共 4 門，Specialization Excel Skills for Business）

Spreadsheet software remains one of the most ubiquitous pieces of software used in workplaces around the world. Learning to confidently operate this software means adding a highly valuable asset to your employability portfolio. Across the globe, millions of job advertisements requiring Excel skills are posted every day. At a time when digital skills jobs are growing much faster than non-digital jobs, completing this course will position you ahead of others, so keep reading. In this last course of our Specialization Excel Skills for Business you will build on the strong foundations of the first three courses: Essentials, Intermediate I + II. In the Advanced course, we will prepare you to become a power user of Excel - this is your last step before specializing at a professional level. The topics we have prepared will challenge you as you learn how to use advanced formula techniques and sophisticated lookups. You will clean and prepare data for analysis, and learn how to work with dates and financial functions. An in-depth look at spreadsheet design and documentation will prepare you for our big finale, where you will learn how to build professional dashboards in Excel.

從本節課中

Data Cleaning and Preparation

Been handed a messy spreadsheet? This module focuses on data cleaning and preparation. You will learn how to use dynamic and automated fixes, clean messy data, automate these processes through functions, and ensure data remains clean dynamically. We will use functions to fix dates, replaces blanks and remove unwanted characters from data – all to help us build an efficient solution that lasts.

Practice Video: Replace blanks with repeating values5:41

Practice Video: Fix Dates (DATE, MONTH, YEAR, DAY, TEXT)6:21

Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)4:42

Practice Video: Diagnostic Tools (ISNUMBER, LEN, CODE)7:21

Practice Video: Remove Unwanted Characters (SUBSTITUTE, CHAR, VALUE)6:12

與講師見面

Dr Yvonne Breyer
Director, Learning and Teaching
Faculty of Business and Economics

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.

探索我們的目錄

免費加入並獲得個性化推薦、更新和優惠。

Coursera 致力於普及全世界最好的教育，它與全球一流大學和機構合作提供在線課程。

© 2018 Coursera Inc. 保留所有權利。

通過 App Store 下載

通過 Google Play 獲取