Text functions: practice exercises
&, LEFT, LOWER, PROPER, TRIM. 6 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In C2, join the first name and surname with a space between them.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | First name | Surname | Full name | Initials | Pasted in | Tidied | ||
| 2 | Grace | Khan | GK | grace.khan@company.co.uk | gRACE khan | Grace Khan | ||
| 3 | Tom | Clarke | TC | tom.clarke@company.co.uk | TOM clarke | Tom Clarke | ||
| 4 | Priya | Ali | PA | priya.ali@company.co.uk | priya ALI | Priya Ali | ||
| 5 | Marek | Smith | MS | marek.smith@company.co.uk | marek SMITH | Marek Smith | ||
| 6 | Nia | Patel | NP | nia.patel@company.co.uk | NIA patel | Nia Patel | ||
| 7 | Ollie | Nowak | ON | ollie.nowak@company.co.uk | ollie NOWAK | Ollie Nowak | ||
| 8 | Lucy | Brown | LB | lucy.brown@company.co.uk | lucy brown | Lucy Brown | ||
| 9 | Hamza | Johnson | HJ | hamza.johnson@company.co.uk | HAMZA johnson | Hamza Johnson |
How it works
& joins text together: =A2&" "&B2. Spaces and other text go in "quotes". CONCAT does the same job.
LEFT(text, n) takes the first n characters. LOWER, UPPER and PROPER change capitals. TRIM removes extra spaces, which is useful for data pasted in from elsewhere.
FIND("-",A2) gives the position of a character, which MID(text, start, how many) can use. TEXT(date,"dddd") shows a date as a day name.
=A2&" - "&B2 =UPPER(LEFT(B2,3)) =PROPER(TRIM(E2)) =MID(A2,FIND("-",A2)+1,3) =TEXT(B2,"mmmm")
Joining and changing text
& joins text together. Anything else you want in between, such as a space or a dash, goes in "quotes".
LEFT(text, n)takes the first n characters,RIGHTthe last, andMID(text, start, n)some from the middle.UPPER,LOWERandPROPERchange the capitals.TRIMremoves extra spaces.FINDgives the position of a character, andTEXTshows a date or number in a format you choose.
Worked example: joining
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Team | Office | Label | Code | Pasted in | Tidied |
| 2 | Sales | Leeds | sales TEAM |
=A2&B2in . & joins the two cells, but with nothing in between: SalesLeeds.=A2&" - "&B2in . Put what you want in between in quotes, and join it in with another &: Sales - Leeds.
Worked example: functions inside functions
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Team | Office | Label | Code | Pasted in | Tidied |
| 2 | Sales | Leeds | sales TEAM |
=UPPER(LEFT(B2,3))in D2.LEFT(B2,3)takes the first 3 characters, Lee.UPPERround the outside makes them capitals: LEE. Excel works from the inside out.=PROPER(TRIM(E2))in F2.TRIMremoves the extra spaces (leaving single spaces between words), andPROPERgives each word a capital: Sales Team.
Worked example: FIND, MID and TEXT
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Ref | Date | Number | Month |
| 2 | AB-731-X | 10/03/2026 |
=FIND("-",A2)in C2.FINDgives the position of a character: the first dash is the 3rd character.=MID(A2,FIND("-",A2)+1,3)in C2.MID(text, start, how many): start one after the dash and take 3 characters: 731. Because FIND finds the dash, it works however long the first part is.=TEXT(B2,"mmmm")in D2.TEXTshows a date in the format in quotes: "mmmm" is the month name, March. "dddd" is the day name, and "ddd" the short one.
What you'll practise
- In C2, join the first name and surname with a space between them.
- In D2, make each employee's initials (e.g. GK).
- In E2, build a lower-case email address like grace.khan@company.co.uk.
- Column G was pasted in from another system. In H2, remove the extra spaces and give each name proper capitals (Grace Khan).
- Order codes look like NTH-4821-B, but the letters before the first dash can be 2, 3 or 4 long. In C2, pull out the 4-digit order number that comes straight after the first dash.
- In C2, show the day of the week of each meeting in full, such as Tuesday.