Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Create Area Chart in Excel
  • How to conditionally sum numeric data in an Excel table using SUMIFS
  • Create Scatter Chart in Excel
  • How To Remove Duplicates In Excel Column Or Row?
  • Understanding Pivot Tables in Excel

References

  • How to use Excel COLUMN Function
  • How to use Excel LOOKUP Function
  • How to use Excel OFFSET function
  • Excel Advanced Lookup using Index and Match Functions
  • Get nth match with INDEX / MATCH in Excel

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Excel Data validation with conditional list
  • Excel Data validation only dates between
  • Excel Data validation must not contain

Extract last name from full name — Manipulating NAMES in Excel

by

If you need extract the last name from a full name, you can do so with this rather complex formula that uses several functions.

Note: In the  formula below, name is a full name, with a space separating the first name from other parts of the name.

Formula

=RIGHT(name,LEN(name)-FIND("*",SUBSTITUTE(name," ","*",
LEN(name)-LEN(SUBSTITUTE(name," ","")))))
Extract last name from full name -- Manipulating NAMES in Excel

Important!

Handling inconsistent spaces

Extra spaces will cause problems with this formula. One solution is to use the TRIM function first to clean things up, then use the parsing formula.

Explanation

In the example, the active cell contains this formula:

=RIGHT(B4,LEN(B4)-FIND("*",SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))))

How this formula works

At the core, this formula uses the RIGHT function to extract characters starting from the right. The other functions which make up the complex part of this formula just do one thing: they calculate how many characters need to be extracted.

Worked Example:   Normalize text by removing punctuations, extra spaces and more in Excel

At a high level, the formula replaces the last space in the name with an asterisk “*” and then uses FIND to determine the position of the asterisk in the name. The position is used to work out how many characters to extract with RIGHT.

How does the function replace only the last space? This is the clever part.

Buckle up, the explanation gets a bit technical.

They key to this formula is this bit:

SUBSTITUTE(B4," ","*",LEN(B4)-LEN(SUBSTITUTE(B4," ","")))

Which does the actual replacement of the last space with “*”.

Worked Example:   Get first name from name with comma -- Manipulating NAMES in Excel

SUBSTITUTE has a forth (optional) argument that specifies which “instance” of the find text should be replaced. If nothing is supplied for this argument, all instances are replaced. However, if, say the number 2 is supplied, only the second instance is replaced. In the snippet above, instance is calculated using the second SUBSTITUTE:

LEN(B4)-LEN(SUBSTITUTE(B4," ",""))

Here, the length of the name without any spaces is subtracted from the actual length of the name. If there’s only one space in the name, it produces 1. If there are two spaces, it the result is 2, and so on.

In the example name in B4, there are two spaces in the name, so we get:

Worked Example:   Count Number of Words in Excel

15 – 13 = 2

And two is used as in the instance number:

SUBSTITUTE(B4," ","*",2)

which replaces the second space with “*”. The name then looks like this:

“Susan Ann*Chang”

The FIND function then takes over to figure out where the “*” is in the name:

FIND("*", "Susan Ann*Chang")

The result is 10 (the * is in the 10th position) which is subtracted from the total length of the name:

LEN(B4)-10

Since the name is 15 characters, we have:

15-10 = 5

The number 5 is used by RIGHT like so:

=RIGHT(B4,5)

Which results in “Chang”

As, you can see, it’s a lot of work above to calculate that simple 5!

Post navigation

Previous Post:

Get first name from full name — Manipulating NAMES in Excel

Next Post:

Get last name from name with comma — Manipulating NAMES in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function example in Excel
  • How to use Excel OR Function
  • Not Equal To ‘<>‘ operator in Excel
  • OR function Examples in Excel

Date Time

  • Steps to create Dynamic calendar grid in Excel
  • How to calculate most recent day of week in Excel
  • Check If Two Dates are same month in Excel
  • DAYS360 function: Description, Usage, Syntax, Examples and Explanation
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Calculate conditional mode with criteria in Excel
  • Group times into 3 hour buckets in Excel
  • Group arbitrary text values in Excel
  • Group numbers with VLOOKUP in Excel
  • How to randomly assign people to groups in Excel

General

  • How to calculate total from percentage in Excel
  • Count cells that do not contain errors in Excel
  • How to choose page/paper size in Excel before Printing
  • Create dynamic workbook reference to another workbook in Excel
  • How to generate random number between two numbers in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning