Excel text function practice: TRIM, LEFT, MID and more
Real data is messy. These exercises use an exported customer list with extra spaces and random capitals. You'll clean names with TRIM and PROPER, pull pieces out of codes with LEFT and MID, and join text together.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
Clean the name in A2: remove the extra spaces and capitalize it properly.
Write your formula in the green cell, G2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
All 11 text exercises
- 1→
Clean up a name
FreeClean the name in A2: remove the extra spaces and capitalize it properly.
- 2→
Store code
FreeGet the 2-letter store code from the start of the order code in D3.
- 3→
Text from the end
PremiumGet the last 4 digits of the order code in D4.
- 4→
Count characters
PremiumHow many characters are in the phone number in C5, including spaces and brackets?
- 5→
Pull out the year
PremiumExtract the year (the 4 digits in the middle) from the order code in D2.
- 6→
Email domain
PremiumGet the domain from the email in B4: everything after the @.
- 7→
Text before a character
PremiumGet the part of the email in B6 that comes before the @.
- 8→
Replace characters
PremiumReformat the phone number in C3 from 555.388.1902 to 555-388-1902.
- 9→
Build a label
PremiumFor row 5, build a label like AW-2024-1120 - Sofia Martinez: the order code, a space, a dash, a space, then the clean name.
- 10→
First name from a messy cell
PremiumGet the first name from A5, cleaned up and capitalized: Noah.
- 11→
Join a list
PremiumJoin all five order codes in D2:D6 into one cell, separated by a comma and a space.
The text functions, with examples
Every example runs on customer-export.xlsx, the same sheet the exercises use, and the results are calculated from that data.
TRIM
Removes spaces at the start and end and squeezes repeated spaces between words down to one.
TRIM(text)Example
=TRIM(A2)returnsemma JOHNSONPROPER
Capitalizes the first letter of each word and lowercases the rest. Wrap TRIM inside it to clean names in one go.
PROPER(text)Example
=PROPER(TRIM(A2))returnsEmma JohnsonLEFT
Returns the first characters of a text. RIGHT returns the last ones.
LEFT(text, [num_chars])Example
=LEFT(D2, 2)returnsNWMID
Returns characters from the middle of a text: give the start position and how many.
MID(text, start_num, num_chars)Example
=MID(D2, 4, 4)returns2026TEXTAFTER
Returns everything after a delimiter, like the domain after the @ in an email. TEXTBEFORE does the opposite. In Microsoft 365 and Excel 2024; older versions need MID and FIND.
TEXTAFTER(text, delimiter, [instance_num])Example
=TEXTAFTER(B2, "@")returnsnorthwind.com
Common mistakes
- 1
Expecting TRIM to remove every space
Text copied from websites often contains non-breaking spaces, which TRIM leaves alone. Swap them for normal spaces first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")). - 2
Forgetting that extracted digits are text
MID returns the text "2026", not the number 2026. A SUM over a column of them gives 0, and a lookup against real numbers finds nothing. Wrap the result in VALUE when you need a number.
- 3
Trusting PROPER with every name
PROPER capitalizes after any non-letter, so liam o'brien becomes Liam O'Brien. But it turns mcdonald into Mcdonald. Check names with prefixes like Mc and Mac by hand.