Spreadsheet Practice

How to compare two columns ignoring extra spaces and case in Excel

Formula (Excel and Google Sheets)

=TRIM(A2)=TRIM(B2)

TRIM both sides and compare with =: =TRIM(A2)=TRIM(B2). The = comparison already ignores upper and lower case.

Result on the example
one result per row in C2, filled down to C7
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in C2, filled down to C7. Change any input and the result updates, or edit the formula to experiment.

How it works

TRIM removes spaces at the ends and turns double spaces into single ones, so "Contoso Ltd " and "Fabrikam Inc" line up with their clean versions. The = operator doesn't care about case, so "ADATUM" equals "Adatum". Only real differences, like "Litware" against "Litware Inc", stay FALSE.

=TRIM(A2)=TRIM(B2)

ResultTRUE

  1. A2"Northwind Traders"

    The value in A2.

  2. TRIM(A2)"Northwind Traders"

    TRIM: Removes extra spaces, leaving single spaces between words.

    • textA2"Northwind Traders"
  3. B2"northwind traders"

    The value in B2.

  4. TRIM(B2)"northwind traders"

    TRIM: Removes extra spaces, leaving single spaces between words.

    • textB2"northwind traders"
  5. TRIM(A2)=TRIM(B2)TRUE

    The = operator tests whether the two sides are equal.

In Google Sheets

The same formula works in Google Sheets: =TRIM(A2)=TRIM(B2). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=A2=B2TRUE, FALSE, FALSE…Different resultThe plain comparison. The rows with extra spaces are FALSE.
=EXACT(TRIM(A2),TRIM(B2))FALSE, TRUE, TRUE…Different resultIgnores spaces but not case. "northwind traders" and "ADATUM" don't match.

Common mistakes

  • Non-breaking spaces from web data survive TRIM. Add SUBSTITUTE(…,CHAR(160)," ") on both sides.
  • Punctuation still counts: "Contoso Ltd." and "Contoso Ltd" differ. Remove periods with SUBSTITUTE if they don't matter.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Compare two columns ignoring extra spaces and case

In C2, return TRUE when the two names match once extra spaces and case are ignored. Fill down to C7.

Questions

How do I ignore all spaces, even between words?

Remove them: =SUBSTITUTE(A2," ","")=SUBSTITUTE(B2," ","").

How do I check whether a name appears anywhere in the other column?

Use COUNTIF on the trimmed value: =COUNTIF($B$2:$B$7,TRIM(A2))>0.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.