substitute/replace function – remove and/or replace text from a cell

By using the SUBSTITUTE or REPLACE functions, you can easily remove or replace a character/s from within the contents of a cell.

In cell A2 type the character string ABCD
In cell C2 type the function =SUBSTITUTE(A2,”BC”,”bc”)

SUBSTITUTE_REPLACE1

A breakdown of the required functions elements – ‘=SUBSTITUTE(cell containing original text, the character string to be substituted, character string to substitute with)

Note that the SUBSTITUTE function IS case sensitive!

 

Another way of performing the same function is to use the REPLACE function as follows

In cell C3 type the function =REPLACE(A2,1,3,”99″)

SUBSTITUTE_REPLACE2

This function required the following elements – ‘=REPLACE(cell containing original text, the character count to start the replace at, the number of characters to replace, the text to replace with)

So in the example above we have started at character 1 of the cell contents – A, for 3 characters – ABC, replace with the specified characters – 99.

Get more great content from Rousseau Associates

Start your project with us today

Call +44 (0)1757 269461

Discuss your requirements today