Excel Text Compare (Table of Contents)
- Compare Text in Excel
- Methods to Compare Tex in Excel
- Compare Text in Excel – Example#1
- Compare Text in Excel – Example#2
- Compare Text in Excel – Example#3
Compare Text in Excel
We compare data in MS Excel occasionally. There are several options as well available to do so in one column, but to determine the matches and differences in the different column we have several techniques to compare it in Excel.
As Excel is versatile, there are several ways to compare the text like full compare or a part of that text, where we can use other functions in Excel (LEFT, RIGHT, INDEX, MATCH, etc.).
Methods to Compare Text in Excel –
Method #1 – EXACT Function
It is a case sensitive, if the two texts are identical then it will return TRUE if not then it will return FALSE.
Ex: There is two text A1 is the ‘String’ and B1 is ‘string’ then result of the EXACT function will be FALSE
=EXACT (A1, B1) >> FALSE
Method #2 – Equal Sign (=)
It is a case insensitive, so when we do not care about the case then we need to prefer this for comparing the text. If the two texts are identical then it will return TRUE if not, then it will return FALSE.
Ex: There is two text A1 is the ‘String’ and B1 is ‘string’ then result of the function will be TRUE
=EXACT (A1, B1) >> TRUE
How to Compare Text in Excel
To Compare text in excel is very easy and simple to use. Let’s understand the working of compare text in excel with few examples.
Compare Text in Excel – Example#1
Comparing for the two City in the employee table in Excel without caring about the case of text, two texts are Home city and Preferred City.
Step 1: Go to Sheet 1 in the Excel sheet which a user wants to compare.
Step 2: The user wants to check Home City and Preferred City, so apply the formula in E column to compare in Excel
Step 3: Click on E2 column and apply equal sign (=), Select B2 cell and put an equal sign and select D2
(= B2=D2)
Step 4: Now click on the Enter button.
Step 5: Apply the above formula to all for that we can drag down on clicking Plus sign of E2 cell.
Summary of Example#1: As we can see in the result of example 1, there is B2 is Melbourne and D2 is Delhi which is not matching so the result is FALSE. Same way in B4 and D4 we have Delhi which is matching so the result is TRUE.
If we see the 5th Row where B2 have Goa and D2 have GOA, their case is different in both the cells, but the equal function will not consider the case as it’s a case insensitive, so the result will be TRUE.
Compare Text in Excel – Example#2
How to Compare the column data in Excel which the user wants to match with the case of text?
Step 1: Go to Sheet 2 in the excel sheet where a user wants to be compared the data, the user wants to check Home city and Preferred city, so apply the formula in E column to compare.
Step 2: Click on E2 cell and apply EXACT function, select B2 cell and apply EXACT function and then select D2
(= EXACT (B2, D2)
Step 3: Now click on the Enter button, the result will be shown based on the input data.
Step4: Apply the above formula to all for that we can drag down on clicking Plus sign of E2 cell in Excel sheet.
Summary of Example#2: As we can see in the result of the sheet 2 example in Excel, there is B2 is Melbourne and D2 is Delhi which is not matching so the result is FALSE. Same way in B4 and D4 have Delhi which is matching so the result is TRUE.
If we see the 5th Row where B2 have Goa and D2 have GOA, their case is different in both the cells, but the EXACT function will consider the case as its case sensitive, so the result will be FALSE.
So, when we need to find the matching or difference in the text string with the case then the EXACT function will do the task, but when we do not care about the case of the text string then we can use equal sign for comparing the data.
Compare Text in Excel – Example#3
How to compare the text in Excel, but the user wants some meaningful result rather than only TRUE/FALSE.
Step 1: Go to Sheet 3 in the excel where a user wants to compare the data, the user wants to check Home City and Preferred City, so apply the formula in E column to compare
Step 2: Click on E2 cell and apply the EXACT function with IF
Step 3: First write IF formula followed by EXACT, like =IF (EXACT (…))
Step 4: Now Select B2 in Text 1 and D2 cells in Text 2 and close the bracket.
Step 5: Now it will ask for value_if_true and value_if_false, put the value for the same.
Step 6: Now give Match if the value is true and Differ if the value is false and close the bracket.
Step 7: Now click on the Enter button, the result will be shown based on the input data.
Step 8: Apply the above formula to all for that we can drag down on clicking the Plus sign of E2 cell.
Summary of Example#3: As we can see in the result of the sheet 3 example, there is B2 is Melbourne and D2 is Delhi which is not matching so the result is Differ. Same way in B4 and D4 have Delhi which is matching so the result is MATCH.
If we see the 5th Row where B2 have Goa and D2 have GOA, their case is different in both the cells, but the EXACT function will consider the case as its case sensitive, so the result will be Differ.
Here we can see on being TRUE we are getting output as Match and when output is FALSE we are getting Differ as the output.
Things to Remember About Text Compare in Excel
- When we are using Equal sign in Excel for comparison, it will treat GOA as goa because equal sign is the case insensitive.
- As EXACT function is case sensitive, when we are not bothered about the case then we can go for equal sign.
- We can use compare the result inside the IF function to show a meaningful message or we can make it a conditional calculation.
- To see the duplicate data in the table, we need to select all the data and Press Ctrl + Q (Quick Analysis) >> then a pop-up will open up >> select Duplicate option >> it will highlight the duplicate.
This is how we can compare the data in the table for duplicate and unique value.
- When we want to compare only a part of text then we can use LEFT and RIGHT function.
Ex.=LEFT(A2,3) =RIGHT(B2,3)
Recommended Articles
This has been a guide to Text compare in excel. Here we discuss How to Compare Text in Excel, Methods for Excel Text Compare along with practical examples and downloadable excel template. You can also go through our other suggested articles –
