How to Compare Two Strings (Text) in Excel (Formula)

- Written by Puneet

excelchamps-free-courses-puneet-gogia

In Excel, you can compare two strings with each other by comparing with two methods:

  • Case Sensitive
  • Non-Case Sensitive

In this tutorial, we will learn to write a formula for both methods.

Compare Two Strings with Non-Case Sensitive Match

  1. First, enter the (=) sign in a cell.
  2. Refer to the cell with the first value.
  3. Again, enter (=).
  4. Refer to the cell with the second value.
  5. In the end, hit enter to get the result.
compare-non-case-sensitive-match

You can see in the above example that in the first cell, you have “Excel,” and in the second cell, you have “EXCEL”. Both values are the same; the only difference is the case. As a result, we have TRUE. As I said, this formula will not consider the case of the text while comparing.

=text1 = text2

Compare Two Strings with Case Sensitive Match

A case-sensitive formula for comparing text strings considers whether the letters are uppercase or lowercase.

For example, “EXCEL” and “Excel” would be considered different strings because one starts with an uppercase “A” and the other with a lowercase “A.” And, if you want to consider the text’s case while matching, you can use the EXACT function. EXACT function checks if two text strings are exactly the same, including uppercase and lowercase letters.

  1. Enter the EXACT function in a cell.
  2. Refer to the cell with the first value.
  3. Type a comma.
  4. Refer to the cell with the second value.
  5. Close the function and hit enter to get the result.
compare-case-sensitive-match

The above formula checks if the text in cell A1 is the same as in cell B1, including the case of each letter. If they are the same, it returns TRUE; if not, it returns FALSE.

Using IF to Compare Two Texts

You can also combine IF with both methods we have discussed above.

if-to-compare-two-texts
=IF(A1=B1,"Yes","No")
=IF(EXACT(A2,B2),"Yes","No")

Get the Excel File

Last Updated: December 02, 2023

Leave a Comment