Excel Text Comparison: Formulas, Case, Spaces, and Errors

With the text values in A2 and B2, compare them in Excel using =A2=B2 for a case-insensitive check or =EXACT(A2,B2) for a case-sensitive one. If A2 contains North and B2 contains north, the first formula returns TRUE and the second returns FALSE.

What Excel text comparison checks

A text comparison asks whether two cell values match according to a chosen rule. The simplest rule is character equality without regard to letter case: Excel treats North and north as equal with the equals operator. A comparison formula returns the logical value TRUE or FALSE, which you can use directly in other formulas or display as a label.

“Matching” can mean different things in practical work. You might want to know whether two cells contain the same text, whether capitalization is identical, whether a value appears anywhere in a list, or whether one string contains a particular word. Those are separate tests. Choosing the right one matters when comparing product codes, names, imported data, or user-entered labels.

Excel does not compare how text looks on screen. Font, color, bold formatting, and number format do not make two text values different. Conversely, characters that are hard to see—such as a trailing space—can make values different even when they appear identical.

Compare two cells with the equals operator

For a direct check, put one value in A2 and the other in B2, then enter this formula in C2:

With A2 containing Maple and B2 containing maple, this comparison returns TRUE because the equals operator ignores case when comparing text.

=A2=B2

Change B2 to Maples and the result becomes FALSE. The formula compares the complete cell contents; it does not check whether one value merely contains the other. For a label instead of TRUE or FALSE, use an IF formula.

With the two candidate strings in A2 and B2, this formula in C2 returns Match when they compare as equal and Different otherwise:

=IF(A2=B2,"Match","Different")

For example, if A2 is Maple and B2 is maple, the displayed result is Match. This can make a review sheet easier to scan, but the underlying result is still based on the same case-insensitive comparison.

Make the comparison case-sensitive

The equals operator is not suitable when uppercase and lowercase letters must be distinguished. Excel’s EXACT function performs a case-sensitive text comparison. It returns TRUE only when the strings match character for character, including capitalization; spaces also count.

With North in A2 and north in B2, enter this formula in C2. The result is FALSE because the first letters have different cases:

=EXACT(A2,B2)

If both cells contain North, EXACT returns TRUE. This is useful for case-sensitive identifiers or when checking whether text was entered with a required capitalization. It does not compare cell formatting, and it should not be confused with a test for whether two cells merely look similar.

You can also use EXACT inside IF to return a readable result. With the candidate values in A2 and B2, this formula returns Case match for identical capitalization and Case differs for any character-level difference:

=IF(EXACT(A2,B2),"Case match","Case differs")

For example, North and north produce Case differs. Use the simpler equals test when case is irrelevant; use EXACT only when capitalization is part of the rule.

Check whether text appears in a list

To test whether one cell’s value occurs in a range, COUNTIF is often convenient. It counts cells meeting a criterion, and the result can be tested against zero. COUNTIF comparisons are case-insensitive, so they are not appropriate if list membership must distinguish North from north.

With the lookup text in A2 and the list in B2:B20, this formula returns TRUE if at least one cell in the list matches A2, and FALSE if none do:

=COUNTIF($B$2:$B$20,A2)>0

If A2 contains Maple and B7 contains maple, the result is TRUE. The dollar signs keep the list range fixed if you copy the formula down a column. COUNTIF also recognizes the wildcard characters * and ?, which can change what counts as a match: an asterisk represents any number of characters, and a question mark represents one character. If the value being searched for contains a literal wildcard, prefix it with a tilde in the criterion, or choose a different comparison method.

For case-sensitive list membership, test each candidate with EXACT rather than relying on COUNTIF. In Microsoft 365, a formula using a dynamic-array function can return multiple results, but a simple copied-down formula or a helper column is often easier to audit. The best approach depends on whether you need one TRUE/FALSE answer, a count, or a list of matching records.

Handle leading, trailing, and hidden spaces

Spaces are characters. A leading space before Maple or a trailing space after it can cause a direct comparison to return FALSE, even though the difference is difficult to spot in a worksheet. Repeated spaces inside a phrase can also matter. Before changing data, decide whether those spaces should be treated as errors or as meaningful content.

For ordinary spaces at the beginning or end, TRIM can normalize both values before comparing them. TRIM also reduces repeated internal spaces to single spaces. With the original strings in A2 and B2, this formula returns TRUE for “ Cedar ” in A2 and “Cedar” in B2:

=TRIM(A2)=TRIM(B2)

TRIM does not remove every character that looks like a space. Data copied from websites or other systems may contain a nonbreaking space, character 160, which can survive an ordinary trim. With the two strings in A2 and B2, the following comparison replaces nonbreaking spaces with regular spaces and then trims the result; it returns TRUE when A2 is “ Cedar ” with a nonbreaking space and B2 is “Cedar”:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))=TRIM(SUBSTITUTE(B2,CHAR(160)," "))

Cleaning text changes the comparison rule, so use a normalized formula only when those characters should be ignored. If the source contains other nonprinting characters, additional cleanup may be necessary. A useful diagnostic is to inspect the cell contents and compare their lengths, rather than assuming a failed match means the visible words differ.

Compare text while highlighting differences

A formula result is useful for analysis, while conditional formatting makes mismatches easier to find in a large sheet. Suppose each row has a value in column A and its counterpart in column B. Select the rows to check, such as A2:B100, and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Set a fill color for the rule and use a relative row reference.

With the paired values in A2 and B2 and the range A2:B100 selected, this rule formula highlights cells in rows where the two entries differ under Excel’s usual case-insensitive comparison:

=$A2<>$B2

The dollar signs lock the compared columns while the row number changes for each row. If capitalization must also match, create the rule using EXACT instead. With the same selected range and paired values starting in row 2, use this formula; it flags North versus north as a difference:

=NOT(EXACT($A2,$B2))

Conditional formatting changes only how cells appear; it does not edit their contents. On Excel for Mac, the Conditional Formatting command is also on the Home tab, though the dialog layout can differ slightly. Excel for the web has conditional formatting too, but some rule-editing details and interface options may vary from the Windows desktop app.

Why a comparison may give an unexpected result

Capitalization: The equals operator ignores case, while EXACT does not. Confirm which rule the task requires.
Invisible characters: Leading or trailing spaces and nonbreaking spaces can make apparently identical values fail a comparison.
Partial matching: A2=B2 checks the complete values. It does not mean “contains,” and COUNTIF can interpret * and ? as wildcards.
Mixed data types: A cell that looks like text may actually contain a number, or a number may be stored as text. Check the source and cell contents before choosing a conversion; changing formats alone does not always convert stored values.
Errors in the cells: If either referenced cell contains an error value, a comparison formula may also return an error rather than TRUE or FALSE. Resolve or deliberately handle the source error instead of treating it as a text mismatch.

For troubleshooting, first inspect the formula bar and verify the referenced cells. Then test the plain comparison and, if needed, test a case-sensitive version or a trimmed version. Avoid applying cleanup to an entire dataset without checking the consequences: punctuation, multiple spaces, and capitalization may be meaningful in account IDs or codes.

Choose the right method for the job

The comparison method should follow the meaning of the data, not just the formula that is shortest. For ordinary names and labels, equals is usually enough. For identifiers where capitalization matters, EXACT gives a stricter test. For searching a list, COUNTIF is readable and efficient when case does not matter; for visible row-by-row review, conditional formatting helps surface exceptions.

Use =A2=B2 for a whole-cell, case-insensitive comparison.
Use =EXACT(A2,B2) when capitalization must match exactly.
Use COUNTIF when checking whether a value appears in a range and wildcard behavior is acceptable.
Use TRIM or SUBSTITUTE only when you have decided that the relevant spaces are unwanted.
Use conditional formatting when people need to scan mismatches across many rows.

For comparisons involving more than two columns, first define whether every value must match a reference cell, whether any pair may match, or whether case and spacing matter. Those requirements lead to different formulas. Writing down the rule in plain language before building a workbook can prevent a formula that is technically correct but answers the wrong question.

Frequently Asked Questions

Is Excel’s equals comparison case-sensitive?
No. A formula such as A2=B2 treats text that differs only by capitalization as equal. Use EXACT when letter case must match.

How do I compare two cells and show Match or No match?
Use an IF formula around the comparison, for example =IF(A2=B2,"Match","No match"). It is case-insensitive; substitute an EXACT test if capitalization matters.

Why do two cells that look identical return FALSE?
One may contain a leading, trailing, repeated, or nonbreaking space, or another invisible character. Inspect the formula bar and test a carefully chosen cleanup method such as TRIM.

How can I compare text in two columns and highlight differences?
Select the paired cells, create a conditional-formatting rule using a formula such as =$A2<>$B2, and choose a format. Make sure the row reference matches the first row of the selected range.

Does COUNTIF distinguish uppercase from lowercase?
No. COUNTIF is case-insensitive and supports wildcard criteria. Use a case-sensitive method based on EXACT when capitalization must be distinguished.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.