In this tutorial, we will use techniques and tools in Excel to compare and analyze text data. We will be using logical operators and the EXACT function to compare texts. In addition, using row differences to compare three or more columns of texts.
Using the equal (=) operator. The equal operator (=) is one of the most basic and essential tools for comparing text in Excel. It can be used to compare the contents of two or more cells and determine if they are identical or not. In this guide, we will show you how to use the equal operator to compare text in Excel.
- Select the cell you want your comparison to appear.
- In the formula bar, type in the equal operator (=) followed by the reference of the first cell. For example, if you want to compare the contents of cell A1 with cell B1, you would type in =A1.
- After the reference of the first cell, add the equal operator (=) followed by the reference of the second cell. In our example, we would type in =A1=B1.
- Press enter, and Excel will return a value of either “TRUE” or “FALSE” depending on whether the contents of the two cells are identical or not.
Note: The equal operator is not case-sensitive, so it will only return “TRUE” if the contents of the cells are identical in terms of spelling.
Using the EXACT() Function. Using the EXACT() function in Excel, you can compare text and see if two cells contain exactly the same characters in order and case. In this guide, we will show you how to use the EXACT() function to compare text in Excel.
- Select the cells that your comparison to appear.
- In the formula bar, type in the function =EXACT( followed by the reference of the first cell. For example, if you want to compare the contents of cell A1 with cell B1, you would type in =EXACT(A1,
- After the reference of the first cell, add a comma (,), and then type in the reference of the second cell. In our example, we would type in =EXACT(A1,B1).
- Press enter, and Excel will return a value of either “TRUE” or “FALSE” depending on whether the contents of the two cells are exactly the same or not.
Note: It is important to note that the EXACT() function compares the entire contents of the cells, including spaces, so it will only return “TRUE” if the contents of the cells are exactly the same in terms of characters, case, and formatting.
Wrapping Boolean Output. You can also use the previous two functions in combination with other functions, such as the IF function, to get more advanced results, like replacing the result of the “TRUE/FALSE” output. =IF(EXACT(A1,B1),”Yes”,”No”) or =IF(A1=B1, “Yes”,”No”), for example, converts “TRUE” to “Yes” and “FALSE” to “No.”
Comparing three or more strings of text. Comparing three or more strings of text can be a bit more challenging than comparing just two. One way to do this is by using row differences in Excel. In this guide, we will show you how to compare three or more strings of text using row differences.
- Highlight all the data that you wish to compare.
- Under the “Home” tab, click “File & Select” and then “Go To Special.”
- Tick “Row Differences” and click “OK.”
- This will highlight all the cells that are not equal to the first column. In this case, the A column.
Conclusion
There are several ways to compare text in Excel, whether it is two or more strings of text. The equals operator (=) for case-insensitive text comparison and the EXACT() function for case-sensitive text comparison—these two features are great for comparing two strings of text. Additionally, using row differences can also be a helpful method to compare three or more strings of text. With the right tools and techniques, you can easily compare and analyze text data in Excel.
