Compare Two Strings for Similarity in Excel (Easiest Way in 2024)
In this article, we will explore how to compare strings in Excel. Simply follow the steps below.
Method 1: Using the Equals Operator to Compare String in Excel
To compare two strings in Excel, we will work with a dataset where book titles entered by two different clerks are listed in Column A (‘Title by First Clerk’) and Column B (‘Title by Second Clerk’). Follow the steps below:
1. Find an Empty Column for Results
Locate an empty column next to the columns where the book titles from the two clerks are entered. This column will be used to display the results of the comparison.
For example, if the titles are in columns A and B, select column C for the results.
2 Input the Equals Operator Formula
In the first cell of your chosen results column (e.g., C1), type the formula =A1=B1. This formula checks if the title in cell A1 matches the title in cell B1. It will return TRUE if the titles are identical, suggesting no errors in entry, and FALSE if they differ.
3 Drag Down the Fill Handle to Apply the Formula to Other Rows
Click on the bottom right corner of cell C1, where the fill handle appears, and drag it down through the column to apply the formula to other rows. This action copies the formula to the remaining cells.
4. See the Results
Review the results in the column where you input the formula. Cells marked TRUE indicate that the titles entered by two clerks match perfectly, while those marked FALSE highlight discrepancies that may need further investigation.
Method 2: Using the IF Function to Compare Two Strings in Excel
To compare strings in Excel, simply follow the process below.
1. Use the IF Function to Output Matching Status
In our example, we have a dataset where book titles entered by two different clerks are listed in Column A (‘Title by First Clerk’) and Column B (‘Title by Second Clerk’). We want to compare these entries using Excel to identify any discrepancies between how the two clerks recorded the titles.
In an empty cell (cell C2), next to the titles, input the formula `=IF(A1=B1, “Match”, “No Match”)`. This formula assesses whether the titles in A1 and B1 are identical.
2. Review the Results
Review the results in the column where you input the IF function. This column will display each comparison as either “Match” or “No Match.” If the result is “Match,” it indicates that both entries for a book title are identical.
However, if the result is “No Match,” it highlights a discrepancy between the two clerks’ entries, suggesting that these particular titles need to be reviewed to ensure the catalog’s accuracy and consistency.
We hope that you now have a better understanding of how to compare two strings for similarity in Excel. If you enjoyed this article, you might also like our article on how to compare tables in Excel or our article on how to save Excel tabs as separate files.
