Course outline / Text functions

Lesson 34 of 75 · Intermediate · 3 exercises

Replacing parts of text

REPLACE by position, SUBSTITUTE a particular occurrence, and count words.

Start this lesson free

What you'll practise

  1. Replace by position with REPLACE
  2. Replace just one occurrence
  3. Count the words

Replace by position with REPLACE

REPLACE swaps characters by position: where to start and how many to remove.

=REPLACE(text, start, number_of_characters, new_text)

In cell C2, replace the 1 character at position 5 of A2 with "/".

Replace just one occurrence

SUBSTITUTE's optional fourth argument picks which occurrence to replace, leaving the others alone.

In cell C2, replace only the 2nd ", " in A3 with " and ".

Count the words

A neat trick counts words: the number of spaces, plus one. LEN with all the spaces removed tells you how many there were.

=LEN(A4)-LEN(SUBSTITUTE(A4," ",""))+1

In cell C2, count the words in A4.

Try it in a real spreadsheet

Every step checks your answer instantly and tells you exactly what to fix.

Start the lesson

Function guides

← Splitting text into partsFormatting dates and numbers as text →