Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)

Loading...

From the course by Macquarie University

Excel Skills for Business: Advanced

423 ratings

Macquarie University

Excel Skills for Business: Advanced

423 ratings

Course 4 of 4 in the 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.

From the lesson

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

Meet the Instructors

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

[SOUND] One thing that can cause a failing major problem in our data

is unwanted spaces.

The main problem with unwanted spaces is that we often can't see them but

Excel can.

So this says CTX and this says CTX space.

We can't see the difference but

Excel is going to treat those values as completely different values.

And that is going to cause us some major problems when we try and

perform calculations and summaries on this data.

And this is exactly the problem Alex is about to encounter.

He's been asked to create a pivot table summarizing the number of members

by organization.

Let's see how to do that.

We're going to come to our Insert Table, and we're going to click Pivot Table, and

we're going to click OK.

We're going to select our organization and

then we're going to do a count on the Last Name.

We are just going to drag Last Name and drop into Values.

And there's our pivot.

But if you have a look down the organization list,

quite a few of them are being repeated.

We can immediately see the problem with the ASET PLC.

This one has a double space, whereas this one only has a single.

The CTX and the DENIL, we can't see what the problem is.

But it's quite likely that there's an extra space added onto the end

of one of those and that's what's causing us the issues.

What we're going to do is clean up these unwanted spaces

using a function called TRIM.

What TRIM does is remove leading, trailing, and extra spaces.

That means spaces before the text, spaces at the end of the text, or

where we have more than one space within the text.

So it will not remove the single spaces but

it will remove one of the double spaces in ASET PLC.

Let's see how it works.

Coming back to our Data (2),

we're going to come to our first organization and double-click.

And just after =, we're going to type in TRIM and press Tab.

We are then going to close our bracket at the end.

And what this is going to do is remove any of those unwanted spaces.

I'm going to click Enter and

then I'm going to double-click to copy the function down.

And we can immediately see that extra space from the ASET PLC has been removed.

Let's go and check how this has impacted output.

Back onto our pivot sheet, we are going to click Analyze and Refresh.

And we have removed the duplicates from ASET and CTX.

So that is one good solution.

You will notice though that we still have a problem with DENIL.

We are still getting duplicates.

A space is what's called a non-printing character.

In other words, it's a character that is there, but it doesn't print and

we can't see it.

Space, however, is just one of many non-printing characters.

And it seems here that we have a different non-printing

character that is being added.

Another function that can help with this is the CLEAN function.

The CLEAN function removes many, but not all, of the non-printing characters.

Let's see if it will help us here.

We're going to come back to Data (2) and

I'm going to double-click on my first calculation again.

And I'd like to do the CLEAN function before the TRIM.

So I'm actually going to click just before Data, and

I'm going to type CLEAN, and then close my bracket at the end, and Enter.

Then, I'm going to double-click to copy down.

We couldn't see those characters in the first place so

we are not seeing any difference yet.

But let's go and double check our pivot table.

Once again, we are going to come, Analyze > Refresh and yes, that has worked.

So we have removed all the duplicates in our organization list.

As I mentioned,

however, the CLEAN function does not clean up all non-printing characters.

In the next video, we're going to look how we can identify

what exactly the non-printing character is.

And then we'll look at how we can clean up other characters that both the TRIM and

the CLEAN won't fix for us.

[SOUND]

Explore our Catalog

Join for free and get personalized recommendations, updates and offers.

Coursera provides universal access to the world’s best education, partnering with top universities and organizations to offer courses online.

© 2019 Coursera Inc. All rights reserved.

Download on the App Store

Get it on Google Play