Function Problems

How to Check if a Full Name Contains First and Last Names in Excel

Muhammad Talha Oct 9, 2026 870 views

Question details

The user needs to find an Excel formula to verify whether a full name string contains specific first and last names, including handling middle names and checking across unaligned datasets.

How to Check if a Full Name Contains First and Last Names in Excel

Product: Excel
Device & OS: not provided
Scenario: Cross-referencing columns of first, last, and full names in a large dataset to ensure the full name encompasses the corresponding first and last names.
Observed behavior: The standard formulas may return FALSE if names are located on different rows (unaligned data), requiring array formulas to search through a whole range instead of a single row.

Before you start

Ensure your columns for first names, last names, and full names do not contain extra leading or trailing spaces, as hidden spaces can cause the matching formulas to fail. You can use the TRIM function to clean your dataset before applying matching formulas.

Solution 1Recommended

Use SEARCH and ISNUMBER for Aligned Rows

This is the most efficient method when your first name, last name, and full name are perfectly aligned on the same row.

The SEARCH function looks for a text string within another text string and is not case-sensitive. By combining it with ISNUMBER and AND, Excel will return TRUE only if both the first and last name are successfully found within the full name.

Select the destination cell

Click on a blank cell where you want to display the TRUE or FALSE result (for example, D2).

Input the formula

Type the formula =AND(ISNUMBER(SEARCH(A2,C2)),ISNUMBER(SEARCH(B2,C2))), assuming A2 contains the first name, B2 contains the last name, and C2 contains the full name.

Apply to other rows

Press Enter to get the result. Click the bottom-right corner of the cell and drag the fill handle down to apply the formula to the rest of your dataset.

Use SEARCH and ISNUMBER for Aligned Rows

Case-Sensitive Matching: If you need the search to be exactly case-sensitive, replace the SEARCH function with the FIND function in your formula.

Solution 2

Use an Array Formula for Unaligned Data

Use this solution when your full name column is not aligned with the first and last name columns, meaning you must search the entire list of names.

Effortlessly Manage Large Datasets with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing capabilities, fully supporting complex array formulas, SEARCH, and MATCH functions. It is highly optimized to handle heavy datasets with tens of thousands of rows smoothly.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the names dataset.
2. Enter the matching formula: Click the target cell and paste your preferred formula, such as =AND(ISNUMBER(SEARCH(A2,C2)),ISNUMBER(SEARCH(B2,C2))).
3. Drag to fill: Double-click the fill handle at the bottom right of the cell to instantly apply the formula to all rows in your large dataset.

100% compatibility with Microsoft Excel formulas and .xlsx filesHigh-performance calculation engine for large datasets (10,000+ rows)Built-in dynamic array support for advanced data matchingFree to use with a familiar, easy-to-navigate interface

microsoft office alternative - wps office

Frequently Asked Questions

Will this formula still work if the full name contains a middle name?

Yes. The SEARCH function looks for the first and last name substrings anywhere within the full name text. Because it doesn't look for an exact cell match, the presence of a middle name in the full name cell will not cause the formula to fail.

Why is my formula returning FALSE even when the names clearly match?

This is often caused by invisible trailing or leading spaces in your cells. Use the TRIM function (e.g., =TRIM(A2)) on your data columns to remove these hidden spaces, or check if your first and last name columns are unaligned with the full name column.

Can I check for a match while being case-sensitive?

Yes. To make the text match case-sensitive, you should use the FIND function instead of the SEARCH function. FIND behaves identically to SEARCH but strictly differentiates between uppercase and lowercase letters.

How do I extract the first and last name instead of checking TRUE/FALSE?

If you want to pull the names out of the full name column, you can use the 'Text to Columns' tool under the Data tab using 'Space' as a delimiter. Alternatively, you can use the LEFT and RIGHT functions combined with FIND to extract specific text.

Excel & Data Problems