Confident Cells

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.

Your turn

In C2, join the first name and surname with a space between them.

ABCDEFGH
1First nameSurnameFull nameInitialsEmailPasted inTidied
2GraceKhanGKgrace.khan@company.co.uk gRACE khanGrace Khan
3TomClarkeTCtom.clarke@company.co.ukTOM clarke Tom Clarke
4PriyaAliPApriya.ali@company.co.uk priya ALIPriya Ali
5MarekSmithMSmarek.smith@company.co.ukmarek SMITHMarek Smith
6NiaPatelNPnia.patel@company.co.ukNIA patel Nia Patel
7OllieNowakONollie.nowak@company.co.uk ollie NOWAKOllie Nowak
8LucyBrownLBlucy.brown@company.co.uklucy brownLucy Brown
9HamzaJohnsonHJhamza.johnson@company.co.uk HAMZA johnsonHamza 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, RIGHT the last, and MID(text, start, n) some from the middle.
  • UPPER, LOWER and PROPER change the capitals. TRIM removes extra spaces.
  • FIND gives the position of a character, and TEXT shows a date or number in a format you choose.

Worked example: joining

ABCDEF
1TeamOfficeLabelCodePasted inTidied
2SalesLeeds sales TEAM
  1. =A2&B2 in . & joins the two cells, but with nothing in between: SalesLeeds.
  2. =A2&" - "&B2 in . Put what you want in between in quotes, and join it in with another &: Sales - Leeds.

Worked example: functions inside functions

ABCDEF
1TeamOfficeLabelCodePasted inTidied
2SalesLeeds sales TEAM
  1. =UPPER(LEFT(B2,3)) in D2. LEFT(B2,3) takes the first 3 characters, Lee. UPPER round the outside makes them capitals: LEE. Excel works from the inside out.
  2. =PROPER(TRIM(E2)) in F2. TRIM removes the extra spaces (leaving single spaces between words), and PROPER gives each word a capital: Sales Team.

Worked example: FIND, MID and TEXT

ABCD
1RefDateNumberMonth
2AB-731-X10/03/2026
  1. =FIND("-",A2) in C2. FIND gives the position of a character: the first dash is the 3rd character.
  2. =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.
  3. =TEXT(B2,"mmmm") in D2. TEXT shows 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

  1. In C2, join the first name and surname with a space between them.
  2. In D2, make each employee's initials (e.g. GK).
  3. In E2, build a lower-case email address like grace.khan@company.co.uk.
  4. Column G was pasted in from another system. In H2, remove the extra spaces and give each name proper capitals (Grace Khan).
  5. 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.
  6. In C2, show the day of the week of each meeting in full, such as Tuesday.