TRIM, CLEAN, UPPER and PROPER Practice Questions with Solutions

Introduction

Text data copied from websites, emails, PDFs, and other systems often contains extra spaces, unwanted characters, or inconsistent capitalization. Excel functions such as TRIM, CLEAN, UPPER, LOWER, and PROPER help clean and standardize this data. In this chapter, you will practice these functions with names, emails, addresses, product data, employee records, and customer information. TRIM, CLEAN, UPPER and PROPER practice questions with solutions to help you understand the concepts.

Question 1: Remove Extra Spaces Using TRIM

Problem Statement

The employee names contain extra spaces between words and at the beginning or end. Use TRIM to clean the names.

Excel Data

Solution

In B2, enter:

=TRIM(A2)

Copy the formula down.

Expected Output

TRIM removes unnecessary spaces and keeps a single space between words.

Question 2: Convert Names to Uppercase Using UPPER

Problem Statement

A customer database contains names in mixed capitalization. Convert all names to uppercase.

Excel Data

Solution

In B2, enter:

=UPPER(A2)

Copy down.

Expected Output

Question 3: Convert Email Addresses to Lowercase

Problem Statement

Email addresses should normally be stored consistently in lowercase. Convert the provided email addresses to lowercase using LOWER.

Excel Data

Solution

In B2, enter:

=LOWER(A2)

Copy down.

Expected Output

Question 4: Format Names Properly Using PROPER

Problem Statement

Convert names written in inconsistent capitalization into proper title case.

Excel Data

Solution

In B2, enter:

=PROPER(A2)

Copy down.

Expected Output

Question 5: Clean Employee Names with TRIM and PROPER

Problem Statement

Employee names contain both unnecessary spaces and inconsistent capitalization. Clean the spaces and convert the names into proper capitalization.

Excel Data

Solution

In B2, enter:

=PROPER(TRIM(A2))

Copy down.

Expected Output

This combines two functions:

• TRIM removes unnecessary spaces.
• PROPER standardizes capitalization.

Question 6: Clean Product Names from Imported Data

Problem Statement

Product names were copied from another system and contain extra spaces and inconsistent capitalization. Clean and standardize them.

Excel Data

Solution

In B2, enter:

=PROPER(TRIM(A2))

Copy down.

Expected Output

Note: PROPER capitalizes the first letter of each word. This means abbreviations such as USB may become Usb.

Question 7: Clean Email Data with TRIM and LOWER

Problem Statement

Email addresses contain unnecessary spaces and inconsistent capitalization. Clean them so they are stored consistently.

Excel Data

Solution

In B2, enter:

=LOWER(TRIM(A2))

Copy down.

Expected Output

This is a common data-cleaning combination because email addresses generally need consistent lowercase formatting.

Question 8: Remove Non-Printable Characters Using CLEAN

Problem Statement

Data copied from another system may contain non-printable characters. Use CLEAN to remove these unwanted characters.

Excel Data

Enter the following values into Excel. Some cells contain hidden line-break or non-printable characters.

Solution

In B2, enter:

=CLEAN(A2)

Copy down.

Expected Output

The unwanted non-printable characters are removed from the cell contents.

Important: CLEAN removes non-printable characters, but it does not remove ordinary extra spaces. Use TRIM when unnecessary spaces are the problem.

Question 9: Combine CLEAN, TRIM and PROPER

Problem Statement

Employee information was imported from an external system. The data contains:

• Unnecessary spaces
• Inconsistent capitalization
• Non-printable characters

Clean the employee names and format them properly.

Excel Data

Solution

In B2, enter:

=PROPER(TRIM(CLEAN(A2)))

Copy down.

Expected Output

This is a practical data-cleaning formula combining three functions.

Question 10: Build a Complete Customer Data Cleaning Formula

Problem Statement

A customer database contains names and email addresses collected from different sources.

Clean both columns using appropriate text functions:

• Customer names should use proper capitalization.
• Email addresses should be lowercase.
• Extra spaces should be removed.
• Non-printable characters should be removed.

Excel Data

Solution

Clean Customer Name

In C2, enter:

=PROPER(TRIM(CLEAN(A2)))

Copy down.

Clean Email

In D2, enter:

=LOWER(TRIM(CLEAN(B2)))

Copy down.

Expected Output

This combines CLEAN, TRIM, PROPER, and LOWER in a practical data-cleaning task.

Key Takeaways

• TRIM in excel removes unnecessary spaces from text.
• CLEAN removes non-printable characters.
• UPPER in excel converts text to uppercase.
• LOWER in excel converts text to lowercase.
• PROPER capitalizes the first letter of each word.
• TRIM does not remove every possible whitespace character from imported data.
• CLEAN and TRIM solve different data-cleaning problems.
• PROPER(TRIM(A2)) is useful for cleaning names.
• LOWER(TRIM(A2)) is useful for standardizing email addresses.
• CLEAN, TRIM, and formatting functions can be combined to clean imported data.
• PROPER may not be appropriate for abbreviations because it can convert USB to Usb.

FAQs

1. What does TRIM do in Excel?

TRIM removes extra spaces from text while keeping a single space between words.

=TRIM(A2)

2. What does CLEAN do in Excel?

CLEAN removes non-printable characters from text.

=CLEAN(A2)

It is useful when data has been copied or imported from another system.

3. What is the difference between TRIM and CLEAN?

TRIM primarily handles unnecessary spaces, while CLEAN removes non-printable characters. They can be combined when imported data has both problems.

4. What does UPPER do in Excel?

UPPER converts all letters in a text value to uppercase.

=UPPER(A2)

5. What does LOWER do in Excel?

LOWER converts all letters to lowercase.

=LOWER(A2)

6. What does PROPER do in Excel?

PROPER converts text so that the first letter of each word is uppercase and the remaining letters are generally lowercase.

=PROPER(A2)

7. Can I combine TRIM and PROPER?

Yes. This is commonly used to clean names.

=PROPER(TRIM(A2))

8. Can I combine CLEAN and TRIM?

Yes. For imported data, you can use:

=TRIM(CLEAN(A2))

This handles non-printable characters and unnecessary spaces.

9. How can I clean an email address in Excel?

A useful formula is:

=LOWER(TRIM(CLEAN(A2)))

It removes unnecessary spaces and non-printable characters and converts the email to lowercase.

10. Should I use PROPER for email addresses?

No. PROPER is generally better suited to names and titles. For email addresses, LOWER is usually more appropriate.

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar