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

Data Analysis

  • Randomize/ Shuffle List in Excel
  • Conflicting Multiple Conditional Formatting Rules in Excel
  • Filter Data Based on Date in Excel
  • How to create Gauge Chart in Excel
  • Example of COUNTIFS with variable table column in Excel

References

  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • Two-way lookup with VLOOKUP in Excel
  • How to get last row in numeric data in Excel
  • How to get address of last cell in range in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation allow weekday only
  • How To Create Drop-down List in Excel
  • Excel Data validation exists in list
  • Excel Data validation unique values only

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

by

If you need extract the last name from a full name in LAST, FIRST format, you can do so with a formula that uses the LEFT and FIND functions. The formula works with names in this format, where a comma and space separate the last name from the first name.

Note: this formula will only work with names in Last, First format, separated with a comma and space.

Formula

=LEFT(name,FIND(", ",name)-1)

Explanation

LAST, FIRST
Philip, White
Tom, Green
Chalk, Black

In the example, the active cell contains this formula:

=LEFT(B4,FIND(", ",B4)-1)

How this formula works

At a high level, this formula uses LEFT to extract characters from the left side of the name. To figure out the number of characters that need to be extracted to get the last name, the formula uses the FIND function to locate the position of “, ” in the name:

FIND(", ",B4) // position of comma

The comma is actually one character beyond the end of the last name, so, to get the true length of the last name, 1 must be subtracted:

FIND(", ",B4)-1 // length of the last name

Because the name is in reverse order (LAST, FIRST), the LEFT function can simply extract the last name directly from the left.

For the example, the name is “Chang, Amy”, the position of the comma is 6. So the formula simplifies to this:

6 – 1 = 5 // length of last name

Then:

LEFT("Chang, Amy",5) // "Chang"

Post navigation

Next Post:

Create One-dimensional and Two-dimensional Array

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

  • How to use Excel NOT Function
  • Extract multiple matches into separate rows in Excel
  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • IF function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Add business days to date in Excel
  • Convert decimal seconds to Excel time
  • How to show last updated date stamp in Excel
  • How to calculate quarter from date in Excel
  • Calculate total hours that fall between two times in Excel

Grouping

  • Group arbitrary text values in Excel
  • Running count group by n size in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel

General

  • Find, Trace and Correct Errors in Excel Formulas using ‘Formula Auditing’
  • Flash Fill in Excel
  • How to set or clear a print area in Excel Worksheet
  • How to create dynamic named range with INDEX in Excel
  • Check if multiple cells have same value with case sensitive in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning