Why text functions clean messy data in Google Sheets - Business Case Study

Scenario Mode

👤 Your Role: You are a data analyst at an online retail company.

📋 Request: Your manager wants you to clean up customer data that has inconsistent text formatting before analysis.

📊 Data: You have a list of customer names and email addresses with extra spaces, inconsistent capitalization, and some unwanted characters.

🎯 Deliverable: Produce a clean version of the customer names and emails using text functions so the data is consistent and ready for reporting.

Sample Data

Customer Name	Email Address
alice JOHNSON	[e-mail]
Bob smith	bob_smith @example.COM
CHARLIE brown	[e-mail]
diana Prince	diana.prince @ example.com
edward NORTON	[e-mail]
fiona O'connor	fiona.o'[e-mail]
george WASHINGTON	[e-mail]
Hannah Montana	[e-mail]

1

Step 1: Remove extra spaces from customer names and emails using the TRIM function.

In a new column for clean names, enter =TRIM(A2) and drag down. For clean emails, enter =TRIM(B2) and drag down.

Expected Result

Customer names and emails no longer have leading or trailing spaces.

2

Step 2: Standardize customer names to proper case (first letter uppercase, rest lowercase) using the PROPER function.

In a new column, enter =PROPER(TRIM(A2)) and drag down.

Expected Result

Names like 'alice JOHNSON' become 'Alice Johnson'.

3

Step 3: Convert all email addresses to lowercase for consistency using the LOWER function.

In a new column, enter =LOWER(TRIM(B2)) and drag down.

Expected Result

Emails like '[e-mail]' become '[e-mail]'.

4

Step 4: Remove any spaces inside email addresses using SUBSTITUTE function.

In a new column, enter =SUBSTITUTE(LOWER(TRIM(B2)), " ", "") and drag down.

Expected Result

Emails like 'bob_smith @example.COM' become '[e-mail]' with no spaces.

5

Step 5: Combine all cleaning steps into one formula for emails to trim, lowercase, and remove spaces.

=SUBSTITUTE(LOWER(TRIM(B2)), " ", "")

Expected Result

Clean, consistent email addresses ready for use.

Final Result

Clean Customer Data
-------------------
Name             | Email
-----------------|---------------------------
Alice Johnson    | [e-mail]
Bob Smith        | [e-mail]
Charlie Brown    | [e-mail]
Diana Prince     | [e-mail]
Edward Norton    | [e-mail]
Fiona O'Connor   | fiona.o'[e-mail]
George Washington| [e-mail]
Hannah Montana   | [e-mail]

✓Extra spaces in names and emails cause inconsistency and errors.

✓Using TRIM removes unwanted spaces around text.

✓PROPER makes names look neat and consistent.

✓LOWER standardizes email addresses to lowercase.

✓SUBSTITUTE removes spaces inside emails to fix formatting.

Bonus Challenge

Create a formula to check if the cleaned email addresses contain exactly one '@' symbol and flag invalid emails.

Show Hint

Use the LEN function combined with SUBSTITUTE to count '@' symbols and IF to flag errors.