Explore
For Enterprise
Join for Free
Log In

Browse
Search
For Enterprise
Log In
Join for Free

Practice Video: Remove Unwanted Spaces (TRIM, CLEAN)

Loading...

Excel Skills for Business: Advanced

Macquarie University

4.7 (739 ratings) | 21K Students Enrolled

Course 4 of 4 in the Excel Skills for Business Specialization

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.

Skills You'll Learn

Dashboard (Business), Financial Modeling, Microsoft Excel, Data Cleansing

Reviews

4.7 (739 ratings)

5 stars
589 ratings
4 stars
123 ratings
3 stars
17 ratings
2 stars
4 ratings
1 star
6 ratings

GK

Aug 05, 2019

Excellent course, well structured and presented. To get the total benefit of this course, I would recommend completing all courses in the Excel Skills for Business Specialization.

CA

Jul 08, 2019

A rewarding experience because I have been able to learn very valuable information. It is an excellent course. It has helped me in the personal and professional field. Thank you.

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

Taught By

Dr Yvonne Breyer
Program Director, Design and Experience, Global MBA

Transcript

[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.

Products

Certificates
Catalog
MasterTrack™ Certificates
Degrees

Enterprise
Government
Campus

Community

Learners
Developers
Beta Testers
Translators
Tech Blog

Coursera

About
Careers
Partners
Press
Blog
Affiliates
Contact
Help

Learn Anywhere

© 2019 Coursera Inc. All rights reserved.

Privacy
Terms
Accessibility
Directory