Concept
1. Cutting text
Invoice number in A2: INV-2026-0457
| Formula | Meaning | Result |
|---|---|---|
=LEFT(A2, 3) |
3 characters from the left | INV |
=RIGHT(A2, 4) |
4 characters from the right | 0457 |
=MID(A2, 5, 4) |
starting at position 5, take 4 | 2026 |
=LEN(A2) |
total characters | 13 |
Spaces and dashes count as characters too.
2. Dynamic cutting with FIND
When the length isn't fixed, find the position of a separator first. Username from an email in B2 (rahul.sharma@gmail.com):
=LEFT(B2, FIND("@", B2) - 1) → rahul.sharma
FIND gives the position of "@" (13); we take everything before it.
3. Cleaning spaces — TRIM
" Rahul Sharma " → =TRIM(A2) → "Rahul Sharma"
TRIM removes spaces at the start and end, and reduces multiple spaces between words to one. This fixes many "why is my lookup showing #N/A?" problems.
4. Hidden characters — CLEAN
Data copied from software or websites often contains invisible line breaks and control characters. =CLEAN(A2) removes them. Use both together:
=TRIM(CLEAN(A2))
Web data trap: the "non-breaking space" (CHAR 160) is not removed by TRIM or CLEAN. Replace it first:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
5. Capitalisation
| Formula | rahul SHARMA becomes |
|---|---|
=UPPER(A2) |
RAHUL SHARMA |
=LOWER(A2) |
rahul sharma |
=PROPER(A2) |
Rahul Sharma |
PROPER capitalises the letter after every non-letter, so o'brien becomes O'Brien but mcdonald's becomes Mcdonald'S. Always check names after using it.
6. Numbers from text are still text
=LEFT("2026ABC", 4) returns "2026" as text (left-aligned). To use it as a number:
=VALUE(LEFT(A2, 4))
Common mistakes
Forgetting that spaces count in LEN and positions. Using TRIM on web data and expecting CHAR(160) to go. Doing maths on LEFT/RIGHT results without VALUE.