People I meet in Microsoft Excel training often tell me they need to compare text in two cells to determine whether the strings are the same or different.
Excel provides several ways to do this. The method you choose will depend on whether the comparison should be case sensitive or not.
Here are 3 ways to compare strings in your Excel spreadsheet.
1) Compare Text Strings with the Equals Sign
The equals sign lets you compare two strings but it doesn’t differentiate between upper and lower case letters. In other words, it’s not case sensitive.
When two strings are the same, the formula will evaluate to TRUE. If the strings are different, the formula will evaluate to FALSE.
In the example (below), I entered =A2=B2 in cell C2. Excel indicates that the text in both cells are the same (TRUE) because the equals sign ignores capitalization.
In cell C6, the formula =A6=B6 evaluates to false. “Oranges” and “Orange” are different.
2) Compare Text Strings with the EXACT Function
The EXACT function lets Excel compare two text strings in Excel and the results are case sensitive.
The EXACT function uses the following syntax:
=EXACT(text1, text2)
If the text strings are the same (including capitalization), the EXACT function will evaluate to TRUE. Otherwise, the EXACT function will evaluate to FALSE.
In the example (below), I typed =EXACT(A2,B2) into cell C2. Excel indicates that the text strings are different because the EXACT function is case sensitive.
3) Compare Text Strings with Conditional Formatting
You can utilize conditional formatting in Excel to compare strings and identify duplicate values but there are a couple of things you’ll want to keep in mind when using this method.
First, condition formatting isn’t case sensitive. It doesn’t differentiate between upper and lower case letters within text strings.
Second, as long as text strings appear more than once with the selected range, conditional formatting will count it as a duplicate – regardless of the word’s position within a column.
In the example (below), the word Apples appears in cell A2 and B5. Conditional formatting ignores capitalization and, since the word appears twice in the selected range, it formats those cells with a green background.
How to Identify Duplicate Values with Conditional Formatting
Here are the steps I used to compare text strings and highlight duplicate values with conditional formatting.
- Drag to select A2:B6.
- Click the Conditional Formatting button in the Home > Styles group.
Additional options appear. - Point to Highlight Cells Rules then select Duplicate Values…
- Configure the Duplicate Values dialog box (as shown) then click OK.
Now You Can Compare Two Strings Like a Pro!
I hope you’ve enjoyed reading this article outlining how to compare strings in Excel. Each method accomplishes the task in a slightly different way.
