Why text manipulation cleans data in Excel - Business Case Study

Scenario Mode

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

📋 Request: Your manager wants you to clean customer contact data to prepare it for marketing campaigns.

📊 Data: You have a list of customer names and phone numbers. Some names have extra spaces, inconsistent capitalization, and phone numbers have different formats.

🎯 Deliverable: Produce a cleaned list where names are properly capitalized without extra spaces, and phone numbers are in a consistent format (only digits, no spaces or symbols).

Sample Data

Customer Name	Phone Number
alice smith	[phone]
BOB JOHNSON	123.456.7891
Charlie Brown	123 456 7892
diana prince	[phone]
edward king	1234567894
Fiona O'connor	123-456-7895
george harris	123 456-7896
Hannah Lee	1234567897

1

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

=TRIM(A2)

Expected Result

"alice smith" (without spaces before or after)

2

Step 2: Capitalize customer names properly using the PROPER function on trimmed names.

=PROPER(TRIM(A2))

Expected Result

"Alice Smith"

3

Step 3: Remove all non-digit characters from phone numbers to keep only numbers.

=TEXTJOIN("", TRUE, IFERROR(MID(B2, ROW(INDIRECT("1:"&LEN(B2))), 1)*1, ""))

Expected Result

"1234567890"

4

Step 4: Apply the formula from step 3 to all phone numbers to standardize their format.

Copy formula down from step 3 for all rows in phone number column.

Expected Result

All phone numbers appear as numbers without spaces or symbols.

5

Step 5: Create a cleaned table with columns: Cleaned Name and Cleaned Phone Number using formulas from steps 2 and 3.

In new columns, use =PROPER(TRIM(A2)) for names and formula from step 3 for phone numbers.

Expected Result

Cleaned names like "Alice Smith" and phone numbers like "1234567890" for all rows.

Final Result

Cleaned Customer Data
---------------------
Name           | Phone Number
---------------|-------------
Alice Smith    | 1234567890
Bob Johnson    | 1234567891
Charlie Brown  | 1234567892
Diana Prince   | 11234567893
Edward King    | 1234567894
Fiona O'Connor | 1234567895
George Harris  | 1234567896
Hannah Lee     | 1234567897

✓Extra spaces in names cause inconsistency and can be removed with TRIM.

✓Proper capitalization makes names look professional and consistent.

✓Phone numbers have many formats; removing non-digit characters standardizes them.

✓Clean data is easier to use for marketing and reduces errors in communication.

Bonus Challenge

Format cleaned phone numbers to the pattern (XXX) XXX-XXXX for better readability.

Show Hint

Use the TEXT function with a custom format or combine LEFT, MID, and RIGHT functions to insert parentheses and dashes.