Course outline / Text functions
Lesson 31 of 75 · Beginner · 13 exercises
Working with text
Split, join and change text with formulas.
What you'll practise
- Take from the left
- Take from the right
- Take from the middle
- Count characters with LEN
- Change case with UPPER
- Small letters with LOWER
- Join text with &
- Join text with CONCAT
- Join a list with TEXTJOIN
- Find the first name
- First word with TEXTBEFORE
- Last word with TEXTAFTER
- Replace text with SUBSTITUTE
Take from the left
Codes and IDs often hide useful parts. LEFT takes characters from the start of some text.
=LEFT(text, number of characters)
In cell E2, get the first 2 characters of the code in B2.
Take from the right
RIGHT works the same way, but takes characters from the end.
In cell E2, use RIGHT to get the last 4 characters of B2.
Take from the middle
MID takes characters from the middle. You give it the text, the position to start from, and how many characters to take.
=MID(text, start, number of characters)
In cell E2, get the 2 characters starting at position 4 of B2.
Count characters with LEN
LEN counts the characters in a cell, including spaces. It's useful for checking that codes are the right length.
In cell E2, use LEN to count the characters in A2.
Change case with UPPER
UPPER converts text to capitals. Its partners are LOWER for small letters and PROPER for capitalising each word.
In cell E2, use UPPER to show the name in A2 in capitals.
Small letters with LOWER
LOWER does the opposite of UPPER and turns every letter into a small one. It's handy for tidying codes and email addresses before you compare them.
In cell E2, use LOWER to show the code in B2 in small letters.
Join text with &
The & symbol joins pieces of text together. Text you type yourself goes in double quotes, including any spaces you want.
=A1&" "&B1 joins A1 and B1 with a space between them.
In cell E2, join A2, then " - " (with its spaces), then B2.
Join text with CONCAT
CONCAT joins its arguments into one piece of text, just like &, but as a function. Separate each piece with a comma, and put any text you type yourself in double quotes.
=CONCAT(A1, " ", B1)
Older versions of Excel call it CONCATENATE, which still works.
In cell E2, use CONCAT to join A2, then " (", then B2, then ")".
Join a list with TEXTJOIN
TEXTJOIN joins a whole range at once and puts a separator between each item. The second argument, TRUE, tells it to skip empty cells.
=TEXTJOIN(separator, TRUE, range)
In cell E2, use TEXTJOIN to list the names in A2:A4, separated by a comma and a space.
Find the first name
FIND tells you the position of a character inside some text. Combined with LEFT, it can pull out everything before the first space, which is the first name.
=FIND(" ", text) returns where the space is.
In cell E2, use LEFT and FIND to get the first name from A2: everything before the space.
First word with TEXTBEFORE
Newer versions of Excel have a simpler way to do the last step. TEXTBEFORE returns everything before a delimiter, the character you split on.
=TEXTBEFORE(text, delimiter)
In cell E2, use TEXTBEFORE to get the first name from A2: everything before the space.
Last word with TEXTAFTER
TEXTAFTER returns everything after a delimiter. With a third argument you choose which one: -1 means the last one.
=TEXTAFTER("Mary Ann Lee", " ") gives Ann Lee.
=TEXTAFTER("Mary Ann Lee", " ", -1) gives Lee.
In cell E2, use TEXTAFTER with -1 to get the last word of the name in A2.
Replace text with SUBSTITUTE
SUBSTITUTE swaps one piece of text for another. To delete something, replace it with empty quotes "".
=SUBSTITUTE(text, old, new)
In cell E2, remove the dash from the code in B2.
Try it in a real spreadsheet
Every step checks your answer instantly and tells you exactly what to fix.