What does it do?

Replaces part of a text string, based on the number of characters you specify, with a different text string

Formula breakdown:

=REPLACE(old_text, start_num, num_chars, new_text)

What it means:

=REPLACE(this cell, starting from this number, all the way to this number, with this new text)


I had a scenario where I wanted to extract the last names from a list of names using a formula.

Did you know that we can creatively use the Replace formula to replace the first name with an empty string, leaving us with the SURNAME?

Here is what we want to happen:

replace-extract-last-name-06

 

I explain how you can do this and please go to the bottom of the page to see the animated gif tutorial:

DOWNLOAD EXCEL WORKBOOK

 

STEP 1: To start off, let us try the Search function and see what it will give us. We want to search on which character the space resides on.   Type in:

=SEARCH(” “, C11)

replace-extract-last-name-00

You will see that it returned 6.

This means our space is on the 6th character of the name Talon Ferguson.

We will use this in our Replace function later in STEP 3.

replace-extract-last-name-01

 

STEP 2: Clear the Search function. We need to enter the Replace function next to the cell that we want to clean the data from:

=REPLACE

 

STEP 3: The Replace arguments:

old_text

Which text do we want to change?

Reference the cell that contains the text string:

=REPLACE(C11,

replace-extract-last-name-02

start_num

Which character do we want to start the replacement from?

We want to replace the first name, which resides on the first character:

=REPLACE(C11, 1,

 replace-extract-last-name-03

num_chars

How many characters do we need to replace?

We don’t have the exact number of characters, so this is where the SEARCH function comes in handy from STEP 1 above.

Search for the space character ” “ which tells us the end of the First Name:

=REPLACE(C11, 1, SEARCH(” “, C11),

replace-extract-last-name-04

new_text

What text will serve as the replacement?

Now that we have accounted for all the characters from the First Name, we need to clear these.

We can do this by replacing it with an empty string.

This will “erase” the First Name, and leave us with the Last Name.

=REPLACE(C11, 1, SEARCH(” “, C11), “”)

replace-extract-last-name-05

 

STEP 4: Do the same for the rest of the cells by dragging the REPLACE formula all the way down using the left mouse button.

Now we have all of the last names:

replace-extract-last-name-06

replace-extract-last-name

HELPFUL RESOURCE:

oztraining_728x90

If you like this Excel tip, please share itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

Share on Google+

Google+

Related Posts

Count Formula in Excel What does it do? Counts the number of cells that contain numbers Formula breakdown: =COUNT(value1, ...) What it means: =COUNT(range of cells to check, ...) Ever had a column of data and wanted to check if all of the values contain valid numbers? It would ...
Round Off Numbers with Excel’s ROUND Formula What does it do? Rounds a number to the nearest decimal based on your specified number of digits Formula breakdown: =ROUND(number, num_digits) What it means: =ROUND(the number, number of decimal places to round off) Ever had the need to round off numbers? ...
Calculate the Compound Interest with Excel’s... What does it do? Calculates the compound interest Formula breakdown: =FV(rate, nper, pmt, ) What it means: =FV(interest rate, number of periods, periodic payment, initial amount) Say that you turn 18 years today (CONGRATS!) and you find out that your parents...
CountBlank Formula in Excel What does it do? Counts the number of cells that are blank Formula breakdown: =COUNTBLANK(range) What it means: =COUNTBLANK(range of cells to check) Do you have a scenario where you want to count the number of cells that are blank in your Excel data? If y...