Using the TRIM & CLEAN Functions to Fix Data in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Using the UPPER and LOWER Functions in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:00 Dealing with Messy Data
  • 0:48 The TRIM Function
  • 2:48 The CLEAN Function
  • 4:16 TRIM and CLEAN Used Together
  • 5:14 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Have you ever received an Excel file with information imported from another database? Did you find extra spaces? Non-printable characters? This lesson explains how to use the TRIM function and its partner, CLEAN.

Dealing With Messy Data

If you have ever imported data from another system into an Excel worksheet, you may have found that the resulting data is a bit messy. To be more specific, you may end up with extra spaces or even non-printable characters.

The extra spaces could be between text or numbers, at the beginning or at the end. The most common non-printable characters are rectangles and squares. This can make the data difficult to work with if you want to run a sort or use other functions.

This lesson will explain how to use the TRIM function in Excel and demonstrate how this function can help you remove the pesky extra spaces. We will also take a look its partner, CLEAN.

The TRIM Function

Excel has over 100 different functions that help you to harness the power of formulas. The TRIM and CLEAN functions are perfect for cleaning up your data. Let's look at an example formula using TRIM.

=TRIM(A1)

Looks harmless enough, right? The above formula tells Excel to remove extra spaces in cell A1.

For instance, say you import a list of names where the first and last names have been merged into one column. Now, for some reason, Excel and the external database didn't play well together. As a result, your text has several spaces before the name, between the first and last name, and even a few extra spaces at the end. You need to clean this up - remove extra spaces - but leave one space between the first and last name. That is the beauty of the TRIM function. It will leave one space between words and remove only the extras!

In our example, the list of names are in column A. So, in column B, we'll add the formula that will clean up the data. Here are the steps:

  1. Select the cell where the formula will be entered, and the new, clean text will appear. In our example, this would be cell B1.
  2. Type =TRIM(
  3. Enter the cell reference. In our example, this would be A1.
  4. Type a closing parenthesis )
  5. Hit Enter

=TRIM(A1)

That's it! You can copy or fill the formula down column B to clean the remainder of the text. You can remember what the TRIM function does by the name alone; it trims extra spaces between words. Now the CLEAN function…

The CLEAN Function

The CLEAN function is not used as much as the TRIM, but it's just as powerful. And, if you plan on pulling data from other systems into an Excel worksheet, you will definitely want this in your tool box.

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Free 5-day trial

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create An Account
Support