Pinterest Pixel

Extract Last Name with Excel’s REPLACE Formula

Bryan
What does it do?
Extract Last Name with Excel’s REPLACE Formula | MyExcelOnline Extract Last Name with Excel’s REPLACE Formula | MyExcelOnline

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:

Extract Last Name with Excel’s REPLACE Formula

 

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

Extract Last Name with Excel’s REPLACE Formula | MyExcelOnline

download excel workbook Replace-Extract-Last-Name-.xlsx

 

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)

Extract Last Name with Excel’s REPLACE Formula

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.

Extract Last Name with Excel’s REPLACE Formula

 

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,

Extract Last Name with Excel’s REPLACE Formula

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,

 Extract Last Name with Excel’s REPLACE Formula

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),

Extract Last Name with Excel’s REPLACE Formula

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), “”)

Extract Last Name with Excel’s REPLACE Formula

 

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:

Extract Last Name with Excel’s REPLACE Formula

 

 

If you like this Excel tip, please share it
Extract Last Name with Excel’s REPLACE Formula | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Learn How to Use ChatGPT in Under 5 Minutes!

Star 30 Days - Full Access Star

One Dollar Trial

$1 Trial for 30 days!

Access for $1

Cancel Anytime

One Dollar Trial
  • Get FULL ACCESS to all our Excel & Office courses, bonuses, and support for just USD $1 today! Enjoy 30 days of learning and expert help.
  • You can CANCEL ANYTIME — no strings attached! Even if it’s on day 29, you won’t be charged again.
  • You'll get to keep all our downloadable Excel E-Books, Workbooks, Templates, and Cheat Sheets - yours to enjoy FOREVER!
  • Practice Workbooks
  • Certificates of Completion
  • 5 Amazing Bonuses
Satisfaction Guaranteed
Accepted paymend methods
Secure checkout

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...