Goal
You’ll be able to use core text functions to clean and combine text data, and understand why real-world spreadsheet data often genuinely needs this kind of cleanup before it can be used reliably.
Learn
Real-world data imported from another system often has hidden problems — extra spaces, inconsistent capitalization, text that needs splitting or combining. Several functions handle this directly:
=TRIM(A1) (removes extra leading/trailing/repeated spaces) =UPPER(A1) (converts text to all uppercase) =LOWER(A1) (converts text to all lowercase) =LEFT(A1, 3) (returns the first 3 characters) =RIGHT(A1, 4) (returns the last 4 characters) =CONCATENATE(A1, " ", B1) (joins text together, with a space between) =A1&" "&B1 (the & symbol does the same thing as CONCATENATE)
TRIM is genuinely important more often than beginners expect — data copied from a website or another system frequently has invisible extra spaces that make two cells look identical to the eye, but fail an exact-match comparison (like in a VLOOKUP) because of that invisible difference. Running TRIM on imported text data is a common, worthwhile first cleanup step before doing anything else with it.
LEFT and RIGHT are useful for extracting a fixed number of characters from the start or end of a text string — like pulling an area code from the beginning of a phone number, or a file extension from the end of a filename.
Decision Task
A VLOOKUP that should be finding an exact match is unexpectedly returning #N/A (not found), even though you can see what looks like an identical value in both places. Before reading on: what specific, invisible problem might genuinely be causing this, and which function could help diagnose or fix it?
Show Answer
Invisible extra spaces — text copied from another source (a website, another system) can contain leading, trailing, or repeated spaces that are genuinely invisible to the eye but make an exact-match comparison fail, since “Value” and “Value ” (with a trailing space) are technically different strings. Applying TRIM to clean the data (=TRIM(A1)) removes exactly this kind of invisible difference, often immediately fixing the failed match.
Common Mistake
Assuming two values that look visually identical must genuinely be identical for exact-match purposes, without considering invisible characters like extra spaces. This is a genuinely common, hard-to-spot cause of lookup functions failing unexpectedly — running TRIM as a diagnostic/cleanup step is often worth trying before assuming something more complex is wrong.
Practice Questions
1. Write a formula that removes extra spaces from the text in cell B1.
Show Answer
=TRIM(B1)
2. Write a formula that returns the first 2 characters of the text in cell C1.
Show Answer
=LEFT(C1, 2)
3. Write a formula that combines the text in D1 and E1 with a space between them, using the & symbol instead of CONCATENATE.
Show Answer
=D1&" "&E1
4. Why might two cells that look visually identical still fail an exact-match comparison in a formula?
Show Answer
One of them might contain invisible extra spaces (leading, trailing, or repeated) that make the two strings technically different, even though they appear identical to the eye.
5. What does the RIGHT function extract, and give a genuine practical use case for it.
Show Answer
The last specified number of characters from a text string; a practical use case is extracting a file extension from the end of a filename, or the last few digits of an ID number.
Try It Yourself
Without looking back, write a formula combining a first name in A1 and last name in B1 into one full name, with a space between them, using the & symbol.
Show Answer
=A1&" "&B1 — joining the two text values with a literal space in between.
Quick Check
1. What does TRIM remove from text?
Show Answer
Extra leading, trailing, and repeated spaces.
2. What does LEFT(A1, 3) return?
Show Answer
The first 3 characters of the text in A1.
3. What does the & symbol do when placed between two cell references?
Show Answer
Joins (concatenates) their text values together, the same as the CONCATENATE function.
4. Why is running TRIM on imported data often a worthwhile first step?
Show Answer
Imported data frequently contains invisible extra spaces that can cause exact-match comparisons and lookups to fail unexpectedly.
5. What does UPPER do to text?
Show Answer
Converts it entirely to uppercase.