SUBSTITUTE Function (Functions in Excel)

Original Text	Old Text To Remove	New Text To Insert	Updated Text
ABCDEF	CD	hello	ABhelloEF	=SUBSTITUTE(B4,C4,D4)
ABCDABCD	CD	hello	ABhelloABhello	=SUBSTITUTE(B5,C5,D5)
Northern Region	Region	Area	Northern Area	=SUBSTITUTE(B6,C6,D6)
Sand and Cement	and	&	S& & Cement	=SUBSTITUTE(B7,C7,D7)

Original Text	Old Text To Remove	New Text To Insert	Instance To Be Replaced	Updated Text
ABCABCABC	ABC	hello	3	ABCABChello
Sand and Cement	and	&	2	Sand & Cement
				=SUBSTITUTE(B10,C10,D10,E10)
				=SUBSTITUTE(B11,C11,D11,E11)

What Does It Do ?

This function replaces a specified piece of text with a different piece of text.

It can either replace all occurrences of the text, or a specific instance.

The function is case sensitive.

Syntax

=SUBSTITUTE(OriginalText,TextToRemove,TextToInsert,InstanceToUse)

The InstanceToUse is optional, if it is omitted all instances will be substituted.

Formatting

No special formatting is needed.

Note

To cope with upper or lower case in the substitution you can use other text functions

such as =UPPER(), =LOWER() or =PROPER() to ensure that the substitution will take place.

Table 1 shows how differing text cases alter the result of the substitution.

Table 1
Original Text	Old Text To Remove	New Text To Insert	Updated Text
Northern Region	Region	Area	Northern Area
Northern region	Region	Area	Northern region
Northern Region	region	Area	Northern Region
Northern Region	Region	area	Northern area
Northern Region	region	area	Northern Region
			=SUBSTITUTE(B39,C39,D39)

Table 2 shows how the =PROPER() function has been used to take account of the mixed cases.

Table 2
Original Text	Old Text To Remove	New Text To Insert	Updated Text
Northern Region	Region	Area	Northern Area
Northern region	Region	Area	Northern Area
Northern Region	region	Area	Northern Area
Northern Region	Region	area	Northern Area
Northern Region	region	area	Northern Area
			=SUBSTITUTE(PROPER(B50),PROPER(C50),PROPER(D50))

Related Posts by Categories