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

Data Analysis

  • Working With Tables in Excel
  • Conditional Formatting New Rule with Formulas in Excel
  • Excel Pie Chart
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to calculate average last N values in a table in Excel

References

  • Convert text string to valid reference in Excel using Indirect function
  • How to get first column number in range in Excel
  • Two-column Lookup in Excel
  • Count unique text values with criteria
  • How to use Excel FORMULATEXT function

Data Validations

  • Excel Data validation unique values only
  • Excel Data validation exists in list
  • Excel Data validation specific characters only
  • Prevent invalid data entering in specific cells
  • Excel Data validation require unique number

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

Previous Post:

Put names into proper case — Manipulating NAMES in Excel

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

  • Return blank if in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • How to use IFS function in Excel
  • TRUE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Calculate time difference in hours as decimal value in Excel
  • Calculate years between dates in Excel
  • MINUTE function: Description, Usage, Syntax, Examples and Explanation
  • Custom weekday abbreviation in Excel
  • Roll back weekday to Friday base on a particular date in Excel

Grouping

  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel

General

  • Index and match on multiple columns in Excel
  • With vs Without Array Formula in Excel
  • Excel Operators
  • AutoRecover file that was never saved in Excel
  • Automatically fill series of cells in Excel using AutoFill
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning