LEFT, RIGHT, MID & FIND practice exercises
LEFT, RIGHT and MID pull characters out of text by position. FIND tells you where a character (like a space or @) is, so you can cut text at the right place even when the lengths differ.
Syntax
LEFT(text, n) · RIGHT(text, n) · MID(text, start, n) · FIND(search, text) · LEN(text)
LEFT / RIGHT- The first or last n characters.
MID(text, start, n)- n characters starting at position start (the first character is 1).
FIND(search, text)- The position of search inside text. Case-sensitive; SEARCH is the case-insensitive version.
The practice data
Customer records with full names, emails, product codes (country-number-color) and messy city names. Every exercise below uses this sheet, and your answer goes in H2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Full name | Product code | City | |
| 2 | Ana Lopez | [email protected] | US-4417-RED | new york |
| 3 | Ben Ortiz | [email protected] | CA-0932-BLU | TORONTO |
| 4 | Cara Smith | [email protected] | US-1205-GRN | chicago |
| 5 | Dev Patel | [email protected] | UK-7781-BLK | London |
| 6 | Eva Ross | [email protected] | US-3390-RED | san diego |
Worked example
=LEFT(C2,2)
The first 2 characters of the product code: the country. Result:
| US |
Your turn: 3 exercises
Type a formula and press Enter. It is checked by a real formula engine, so any correct approach counts.
1 Return the 4-digit number from the product code in C3.
2 Return the color code (last 3 characters) of the product code in C4.
3 Return the first name from A5, using FIND to locate the space.
Master LEFT, RIGHT, MID & FIND in the full course
Lesson 7 of 18 goes deeper, with more exercises in a real spreadsheet grid. Start with the first 4 lessons free, then unlock everything with Pro.
Start free See pricingCommon LEFT, RIGHT, MID & FIND mistakes
- Off-by-one errors: MID counts from 1, and you usually need FIND(...)-1 to exclude the separator itself.
- Hard-coding lengths when names vary. Use FIND or LEN instead of a fixed number.
- Expecting numbers back. MID("4417"...) returns text; wrap it in VALUE() to calculate with it.
FAQ
How do I get the last name?
=MID(A2,FIND(" ",A2)+1,LEN(A2)) returns everything after the first space.
Is there an easier way to split text?
Yes: SPLIT in Google Sheets (TEXTSPLIT in Excel 365). See the SPLIT exercises.