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

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

**Excel:** `=TRIM(A2)=TRIM(B2)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C |
| --- | --- | --- |
| CRM name | Billing name | Same? |
| Northwind Traders | northwind traders |  |
| Contoso Ltd | Contoso Ltd |  |
| Fabrikam  Inc | Fabrikam Inc |  |
| Litware | Litware Inc |  |
| Adatum | ADATUM |  |
| Proseware | Proseware |  |

Formula in C2, filled to C7: `=TRIM(A2)=TRIM(B2)`

Result: TRUE; TRUE; TRUE; FALSE; TRUE; TRUE

## 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.

## Other ways

- `=A2=B2`: The plain comparison. (The rows with extra spaces are FALSE.)
- `=EXACT(TRIM(A2),TRIM(B2))`: Ignores 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.

## 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`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/compare-two-columns-ignoring-spaces