- Extract parts of text with LEFT, RIGHT, MID and LEN
- Clean data with TRIM, UPPER and LOWER
- Join text with
&and CONCAT
Sign-ups for the robotics club came from an online form, and the list is a mess: some names are in lower case, some in CAPITALS, some have extra spaces. You also need to pull the city and year out of each member's code, such as BAK-2026-017. Fixing 200 rows by hand would take hours — text functions do it with a few formulas.
Cutting text: LEFT, RIGHT, MID, LEN
Formula (A2 = BAK-2026-017) | Result | Explanation |
|---|---|---|
=LEFT(A2,3) | BAK | 3 characters from the left |
=RIGHT(A2,3) | 017 | 3 characters from the right |
=MID(A2,5,4) | 2026 | 4 characters starting at the 5th |
=LEN(A2) | 12 | number of characters (hyphens and spaces count too) |
Cleaning up: TRIM, UPPER, LOWER, PROPER
=TRIM(A2)— removes spaces at the start and end and leaves just one space between words: “ Leyla Hasanova ” → “Leyla Hasanova”.=UPPER(A2)— makes every letter a capital: “baku” → “BAKU”.=LOWER(A2)— makes every letter lower case: “MURAD” → “murad”.=PROPER(A2)— capitalises the first letter of each word: “aysel nuriyeva” → “Aysel Nuriyeva”.
Joining text: &, CONCAT, TEXTJOIN
If the first name is in A2 and the surname in B2, you can build the full name in two ways: =A2&" "&B2 or =CONCAT(A2," ",B2). Don't forget the space between them — it is a separate piece of text in quotes. The older CONCATENATE function still works, and TEXTJOIN in Microsoft 365 joins a whole range with a separator: =TEXTJOIN(", ",TRUE,A2:A10).
A2 contains Murad and B2 contains Quliyev. Build a lower-case login from the first letter of the first name plus the surname, and an email address of the form name.surname@example.com.
Show solutionHide solution
=LOWER(LEFT(A2,1)&B2) → “M” & “Quliyev” → mquliyev.Email:
=LOWER(A2&"."&B2)&"@example.com" → murad.quliyev@example.com.Functions work inside each other: the inner part is calculated first, then the outer function.
Key points
- LEFT/RIGHT take characters from the ends, MID from the middle; LEN counts characters.
- Text functions return text; use VALUE when you need a number.
- TRIM removes extra spaces; UPPER, LOWER and PROPER change letter case.
- Join text with
&or CONCAT; add the space in between as" ".
Check yourself
10 questions. Every correct answer earns XP.
=LEFT("Excel",2) return?