Purpose
By the end of this lesson, you will be able to use text functions to clean and standardize messy imported or pasted data.
Lesson Explanation
TRIM removes extra spaces from text, leaving single spaces between words: =TRIM(A2) turns ” John Smith ” into “John Smith” – essential after importing data from another system, where invisible extra spaces commonly cause lookup and matching failures.
UPPER, LOWER, and PROPER change text case: =PROPER(A2) turns “john smith” into “John Smith,” useful for standardizing inconsistently capitalized name or address fields.
LEFT, RIGHT, and MID extract portions of text: =LEFT(A2,3) returns the first 3 characters; =RIGHT(A2,4) returns the last 4 characters; =MID(A2,4,2) returns 2 characters starting from the 4th position – useful for pulling a specific code or prefix out of a longer identifier.
CONCATENATE (or the & operator) joins text from multiple cells: =A2&” “&B2 combines a first name in A2 and last name in B2 into “John Smith” with a space between them.
SUBSTITUTE replaces specific text within a cell: =SUBSTITUTE(A2,”-“,””) removes all hyphens from a phone number or ID field, useful for standardizing formats before comparison or lookup.
Practice Questions
1. A pasted dataset has entries like ” Jane Doe ” with inconsistent extra spaces. Write a formula to clean this up.
View Answer
=TRIM(A2)2. A “Name” column has inconsistent capitalization: “JANE DOE,” “jane doe,” “Jane doe.” Write a formula to standardize these to proper case (“Jane Doe”).
View Answer
=PROPER(A2)3. A product code always starts with a 3-letter category prefix, like “ELE-4829” for electronics. Write a formula to extract just the first 3 characters.
View Answer
=LEFT(A2,3)4. The same product code ends with a 4-digit number after the prefix and hyphen. Write a formula to extract the last 4 characters.
View Answer
=RIGHT(A2,4)5. A “First Name” column is in A2 and a “Last Name” column is in B2. Write a formula to combine them into a single “Full Name” with a space between, e.g., “John Smith.”
View Answer
=A2&” “&B26. A column of phone numbers contains inconsistent formatting, some with hyphens like “555-123-4567” and some without. Write a formula that removes all hyphens from cell A2.
View Answer
=SUBSTITUTE(A2,”-“,””)7. A VLOOKUP formula is failing to match values that look identical on screen. What text-cleaning function, applied to both the lookup value and the data being searched, is a common fix for this?
View Answer
TRIM – invisible extra spaces are a very common cause of lookups that appear to match visually but fail technically.
8. A dataset has employee IDs formatted inconsistently as “emp001,” “EMP001,” and “Emp001.” Which function would standardize all of these to consistent uppercase, “EMP001”?
View Answer
UPPER – =UPPER(A2).
9. An ID field is structured as “COUNTRY-REGION-CODE” (e.g., “US-WEST-4829”), and a report needs to extract just the middle “REGION” portion, which varies in length. Why might MID alone be insufficient for extracting this cleanly, given varying lengths?
View Answer
MID requires knowing the exact starting position and length to extract, which becomes unreliable if the middle section’s length or position varies between rows; a more robust approach would likely combine MID with functions like FIND to locate the hyphens dynamically.
10. A dataset combines a “City” and “State” column into one “Location” field using =A2&”, “&B2. What would this formula produce for A2=”Chicago” and B2=”IL”?
View Answer
“Chicago, IL”
11. A COUNTIF formula is undercounting matches for “Closed” because some entries have trailing spaces like “Closed “. What single formula change to a helper column would resolve this before counting?
View Answer
Applying =TRIM(A2) to create a cleaned version of the column first, then running COUNTIF against that cleaned column instead of the original.
12. A phone number field needs both hyphens removed AND to be checked for correct 10-digit length after cleaning. Write a formula combining SUBSTITUTE and LEN to return TRUE if the cleaned number is exactly 10 digits.