Get Last Name From Name With Comma

To get the last name from a name written as “Last, First” (comma-separated format), you take everything before the first comma. For example, in “Smith, John” the last name is “Smith”; in “van der Berg, Anna” the last name is “van der Berg”. This article explains the logic, common pitfalls, and how to do it in Excel, Google Sheets, and programming languages.

Why Comma Order Matters

In Western naming conventions, the comma-separated format almost always means “Last Name, First Name”. This is common in bibliographies, mailing lists, and databases. However, in some cultures (e.g., Hungarian, Japanese, Chinese), the family name comes first even without a comma. When you see a comma, it’s a strong indicator that the part before the comma is the surname. But be aware: if the data entry was inconsistent, the comma might be missing or placed differently. Always check a few records to confirm the pattern.

How to Do It in Excel and Google Sheets

Both Excel and Google Sheets use the same formula syntax. To extract the last name from cell A1 containing “Smith, John”:

• Last name: =TRIM(LEFT(A1, FIND(",", A1) - 1))
• First name: =TRIM(MID(A1, FIND(",", A1) + 1, LEN(A1))) (or RIGHT as shown above).

If some names don’t have a comma, the FIND function returns an error. To handle that, use IFERROR:

• =IFERROR(TRIM(LEFT(A1, FIND(",", A1) - 1)), A1) – this returns the whole cell if no comma is found, assuming the entire string is the last name.

In Programming Languages

In Python, you can split by comma:

full = "Smith, John"
last = full.split(",")[0].strip()
first = full.split(",")[1].strip() if "," in full else ""

In JavaScript:

let full = "Smith, John";
let parts = full.split(",");
let last = parts[0].trim();
let first = parts[1] ? parts[1].trim() : "";

In SQL (SQL Server):

SELECT LTRIM(RTRIM(LEFT(full_name, CHARINDEX(',', full_name) - 1))) AS last_name

In R:

last <- trimws(strsplit(full, ",")[[1]][1])

The principle is the same everywhere: split on the comma and take the first piece.

Common Mistakes and Edge Cases

• Spaces after the comma: “Smith, John” has a space after the comma. If you don’t trim, the first name becomes “ John” with a leading space. Always trim.
• Multiple commas: “Smith, John, Jr.” – the last name is still “Smith”, but the first name would be “John, Jr.” if you take everything after the first comma. Decide whether to keep suffixes separate.
• No comma: “John Smith” – the formula fails. Use IFERROR or check for comma presence.
• Empty cell: Handle with IF(A1=””,””,…).
• Last name with spaces: “van der Berg, Anna” – the formula correctly returns “van der Berg” because it takes everything before the comma, not just the last word.
• Middle initials: “Smith, John A.” – first name becomes “John A.”, which may be fine.

Alternatives to Comma Format

If your data is in “First Last” format (no comma), extracting the last name is trickier because you need to find the last space. In Excel, you can use:

=TRIM(RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))))))

This finds the last space and takes everything after it. In Python, you can use full.split()[-1]. But this fails for multi-word last names like “van der Berg”. That’s why the comma format is preferred in databases – it removes ambiguity.

Frequently Asked Questions

What if the name has a suffix like “Smith, John Jr.”?

The last name is still “Smith”. The first name would be “John Jr.” if you take everything after the comma. If you want to separate the suffix, you’d need additional logic to detect “Jr.”, “Sr.”, “III”, etc., which is beyond a simple comma split.

How do I handle names without a comma?

Use an IFERROR or IF condition to check if a comma exists. If not, you can either leave the cell unchanged or assume the whole string is the last name (if your data is supposed to be in last-first order).

Can I extract the last name from a full name in “First Last” format?

Yes, but it’s less reliable. You can take the text after the last space, but that fails for compound last names. The comma format is unambiguous; the space-separated format is not.

Why does my formula return an error when the cell is empty?

FIND(“,”, “”) returns an error because there’s no comma. Wrap the formula in IF(A1=””,””,…) to return a blank instead.

Does this work for names with multiple commas like “Smith, John, MD”?

Yes, the last name is still everything before the first comma. The rest after the first comma is treated as the first name (including any additional commas). If you need to separate credentials, use a more advanced parsing method.

Related

• Create Kid Profile Hulu: Step-by-Step Setup
• GitHub CLI Commands: The Essential Guide
• Customize Safari Settings for Individual Websites