Have you ever needed to replace part of a string (i.e. text entry) with something else, across multiple cells? For example:

  • You need to replace every occurrence of a space with an underscore.
  • You need to replace the second occurrence of ABC with DEFG.
  • You need to insert a dash between the 5th and 6th characters.

The REPLACE function, the SUBSTITUTE function and good old Find and Replace are 3 ways that can be used to do this.

This video covers all 3 methods, explaining which is the best one to use as well as the gotchas you need to watch out for.

Links to the video and individual sections below…


Find and Replace – no functions needed

The Substitute function

Substitute – replace the Nth occurrence

The Replace function

Use the Replace function to insert text within a string