Function Problems

How to Extract First and Last Names from Email Addresses in Excel

Maira Mehtab Oct 10, 2026 868 views

Question details

The user needs to separate email addresses formatted as first.last@domain.com into distinct first name and last name columns.

How to Extract First and Last Names from Email Addresses in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Managing and organizing a large list of email addresses in a spreadsheet by parsing out the individual names for customized outreach or data entry.
Observed behavior: The first and last names are combined within a single email address string separated by a period, and need to be isolated into individual cells automatically.

Before you start

Verify that your version of Excel supports the TEXTBEFORE and TEXTAFTER functions, which are generally available in Microsoft 365 and Excel for the Web. Also, ensure your email list consistently follows the 'first.last@domain' format.

Solution 1Recommended

Use TEXTBEFORE and TEXTAFTER Functions

This is the most efficient and straightforward method for Microsoft 365 users to extract names using standard email delimiters.

The TEXTBEFORE and TEXTAFTER functions allow you to extract specific parts of a text string based on a delimiter. For an email like 'john.doe@hotmail.com', we can use the period (.) and the at symbol (@) as delimiters to isolate the first and last names.

Extract the first name

Select an empty cell adjacent to your first email address (e.g., cell B1 if the email is in A1). Type the formula =TEXTBEFORE(A1, ".") and press Enter. This extracts all text before the first period.

Extract the last name

Select the next empty cell (e.g., cell C1). Type the formula =TEXTBEFORE(TEXTAFTER(A1, "."), "@") and press Enter. This extracts the text that comes after the first period but before the at symbol.

Apply formulas to the entire list

Select both formula cells (B1 and C1). Click and drag the fill handle (the small square at the bottom-right corner of the selection) down the columns to apply these formulas to the rest of your email list.

Handling Different Formats and Errors: If some email addresses lack a period or contain additional characters, the formula may return an error. You can wrap your formula in an IFERROR function, such as =IFERROR(TEXTBEFORE(A1, "."), ""), to leave the cell blank instead of displaying an error message.

Advanced Spreadsheet Data Tool

Extract Email Data Effortlessly with WPS Spreadsheet

WPS Spreadsheet is a highly capable and user-friendly tool that seamlessly handles advanced data extraction. It offers full support for modern text manipulation features, allowing you to split and organize email lists in seconds.

1. Open your file in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet application, and load the document containing your email address list.
2. Use Flash Fill for automatic extraction: Type the correct first name next to the first email address in the adjacent column and press Enter. Then, select the cell below it and press Ctrl+E. WPS Spreadsheet will automatically detect the pattern and fill in the remaining names.
3. Utilize text formulas: Alternatively, you can type standard Excel-compatible text formulas directly into the formula bar to parse emails based on delimiters like periods and @ symbols.

Seamless compatibility with Microsoft Excel formats (.xlsx) and standard text formulasIncludes intelligent Flash Fill capabilities to automatically detect and extract name patterns from emailsLightweight and optimized for fast performance on both small and massive datasetsFree, intuitive interface that requires zero learning curve for existing Excel users

Frequently Asked Questions

What should I do if my Excel version does not support TEXTBEFORE and TEXTAFTER?

If you are using an older version of Excel, you can use the Flash Fill feature by manually typing the first name and pressing Ctrl+E. Alternatively, use a combination of legacy formulas, such as =LEFT(A1, FIND(".", A1)-1) for the first name.

How can I extract names if the email does not contain a period?

If the email is formatted like 'johndoe@domain.com' without a clear delimiter between the first and last name, standard formulas cannot separate them automatically. You will need to rely on manual separation or utilize Flash Fill to teach Excel the pattern through manual corrections.

How do I extract only the domain name from the email address?

To extract the domain name (e.g., 'hotmail.com'), you can use the TEXTAFTER function. Assuming the email is in cell A1, simply type =TEXTAFTER(A1, "@") and press Enter. This returns everything following the at symbol.

Excel & Data Problems