How to use Excel Replace function

Version in which REPLACE function can work:

Excel REPLACE function will work in Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel 2016 for Mac Excel for Mac 2011 Excel Online Excel for iPad Excel for iPhone Excel for Android tablets Excel Starter Excel Mobile Excel for Android phones.

About REPLACE function:

The Excel REPLACE function replaces the text based on the location which we provide in the formula.

Syntax for excel REPLACE Function:

=REPLACE(old_text,start_num,num_chars,new_text)

Parameters:

old_text: The text to be replaced.

start_num: starting value of the text to search

num_chars: The number of characters to be replaced.

new_text: The text to replace old_text. If you want remove the old_text then you need not give any input for this parameter.

Using REPLACE function:

In the image shown below if you want to remove the word “Mr.”  then the formula u need to put:

Words Formula Result
Mr. X  =REPLACE(A3,1,3,)  X
Mr. Y  =REPLACE(A4,1,3,)  Y
Mr. Z  =REPLACE(A5,1,3,)  Z
Mr. A  =REPLACE(A6,1,3,)  A
Mr. B  =REPLACE(A7,1,3,)  B
Mr. C  =REPLACE(A8,1,3,)  C

excel replace function

If you want to replace “Mr.” with “Mrs.” then formula will be like this:

Words Formula Result
Mr. X  =REPLACE(A3,1,3,”Mrs.”) Mrs. X
Mr. Y  =REPLACE(A4,1,3,”Mrs.”) Mrs. Y
Mr. Z  =REPLACE(A5,1,3,”Mrs.”) Mrs. Z
Mr. A  =REPLACE(A6,1,3,”Mrs.”) Mrs. A
Mr. B  =REPLACE(A7,1,3,”Mrs.”) Mrs. B
Mr. C  =REPLACE(A8,1,3,”Mrs.”) Mrs. C

Leave a Comment