← All lessons
Spreadsheets · Lesson 7 of 10

Working with text

& joins text: =A2&" "&B2 puts a first and last name together with a space between. The space is text too, so it goes in quotes.

LEFT(text, n) and RIGHT(text, n) take the first or last n characters; MID(text, start, n) takes some from the middle. LEN(text) counts the characters, spaces included.

Imported or typed data is often messy. TRIM removes extra spaces, UPPER and LOWER change case, and PROPER capitalises each word. Functions can sit inside each other: =PROPER(TRIM(A2)) tidies the spaces, then fixes the capitals.

Practice

Put the first and last names together, with a space, in C2 (copied to C4).

ABC
1FirstLastFull name
2AdaLovelace
3AlanTuring
4GraceHopper

C2

More info

Quick reference

&Joins text.
LEFT / RIGHT(text, n)The first / last n characters.
MID(text, start, n)n characters from position start.
LEN(text)How many characters.
TRIM, UPPER, LOWER, PROPERTidy spaces; change case.

The practice answers

Other formulas that give the same results count too.

  1. Put the first and last names together, with a space, in C2 (copied to C4). =A2&" "&B2
  2. Now just the initials, like AL, in C2 (copied to C4). =LEFT(A2,1)&LEFT(B2,1)
  3. These names were typed in a hurry. Remove the extra spaces and fix the capitals in B2 (copied to B4). =PROPER(TRIM(A2))
  4. Each code is a two-letter country, a dash and a number. Take the country part in B2 (copied to B4). =LEFT(A2,2)
  5. How long is each headline? Count the characters in B2 (copied to B4). =LEN(A2)

An unofficial course. Not made by or affiliated with Microsoft; Excel is a trademark of Microsoft Corporation.