Function Problems

How to Find Excel Rows Containing Only Two Names

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs a method to identify and highlight specific rows within a large dataset of about 2,000 records that contain exactly two words (a person's name and father's name), omitting the third word (grandfather's name).

Product: Excel
Device & OS: not provided
Scenario: Auditing or filtering incomplete naming records in an Excel spreadsheet where some entries have three names and others have only two.
Observed behavior: The user requires a formula or tool to distinctively identify cells containing exactly two text strings separated by a space.

Before you start

Before applying text-counting formulas, ensure that the names in your cells are separated by single spaces, as leading, trailing, or double spaces can cause the formula to return inaccurate results.

Solution 1Recommended

Use Conditional Formatting with a Character Counting Formula

This method highlights cells with exactly two names by counting the spaces between them. Two names separated by a single space will contain exactly one space character.

The most effective way to identify the number of words in a cell is by counting the spaces. The formula works by calculating the total length of the cell's text and subtracting the length of the same text after removing all spaces.

If the difference is exactly 1, it means there is exactly one space in the cell, which corresponds to exactly two names.

Select your data range

Highlight the range of cells containing the names you want to check (for example, A2:A2000).

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon and click on 'Conditional Formatting', then select 'New Rule' from the drop-down menu.

Choose the formula option

In the New Formatting Rule dialog box, select 'Use a formula to determine which cells to format'.

Enter the space-counting formula

Input the formula: =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1 (make sure 'A2' matches the first cell in your selected range).

Apply formatting

Click the 'Format' button, choose a distinct fill color to highlight the matching cells, and click 'OK' twice to apply the rule.

Handling Extra Spaces: If your dataset might contain accidental double spaces, wrap your cell references in the TRIM function like this: =LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2)," ",""))=1.

Efficient Data Management with WPS Office

Easily Find and Format Data with WPS Spreadsheet

WPS Spreadsheet provides powerful conditional formatting and text formulas to help you quickly identify specific data patterns, like isolating specific names in large lists, completely free of charge.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your list of names.
2. Select the data range: Click and drag to highlight the column containing the records you want to check.
3. Apply Conditional Formatting: Go to Home > Conditional Formatting, select 'New Rule', and input the LEN and SUBSTITUTE formula.
4. Set the highlight style: Choose a background color and apply the rule to instantly view rows with exactly two names.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Intuitive interface for applying complex formatting rules to large datasets effortlessly.Lightweight software that processes thousands of rows quickly without lagging.Free built-in data cleaning tools to remove extra spaces instantly.

microsoft office alternative - wps office

Frequently Asked Questions

How can I extract or filter the rows with two names instead of highlighting them?

Instead of using Conditional Formatting, you can insert a helper column next to your data. Enter the formula =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=1 in the new column and drag it down. It will return TRUE or FALSE. You can then use the Filter tool (Data > Filter) to only show rows where the helper column is TRUE.

Can I use this formula to find rows with exactly three names?

Yes. Three names will have exactly two spaces between them. You can modify the end of the formula to equal 2 instead of 1: =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=2.

Why is the formula not highlighting cells that clearly only have two names?

This usually happens because of hidden characters, trailing spaces at the end of the text, or leading spaces at the beginning. Use the =TRIM(A2) function in a separate column first to clean your data, and then apply the conditional formatting to the cleaned data.

Excel & Data Problems