EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips Compare Text in Excel

Compare Text in Excel

Arun Gupta
Article byArun Gupta
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated June 3, 2023

Excel Text Compare

How to Compare Text in Excel?

In this article, we will learn about compare text in Excel. We compare data in MS Excel occasionally. Several options are available to do so in one column, but to determine the matches and differences in the different columns, we have several techniques to compare it in Excel.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

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

The following method shows how to compare text in Excel.

Method #1 – EXACT Function

If the two texts are identical, it is case sensitive; then it will return TRUE if not, it will return FALSE.

Ex: There are two texts A1 is the ‘String’, and B1 is the ‘string’, then the result of the EXACT function will be FALSE

=EXACT (A1, B1) >> FALSE

Method #2 – Equal Sign (=)

It is case insensitive, so we must prefer this to compare the text when we do not care about the case. If the two texts are identical, it will return TRUE; if not, it will return FALSE.

Ex: There are two texts A1 is the ‘String’, and B1 is the ‘string’ then the result of the function will be TRUE

=EXACT (A1, B1) >> TRUE

How to Compare Text in Excel?

Compare text in Excel is very easy and simple to use. Let’s understand how to compare text in Excel with a few examples.

You can download this Text Compare Excel Template here – Text Compare Excel Template

Compare Text in Excel – Example#1

Comparing the two Cities 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 the E column to compare in Excel

Step 3: Click on the E2 column and apply equal sign (=), Select B2 cell and put an equal sign, and select D2

(= B2=D2)

Compare Text Example 1-1

Step 4: Now click on the Enter button.

Compare Text Example 1-2

Step 5: Apply the above formula to all; we can drag it down by clicking the Plus sign of the E2 cell.

Compare Text Example 1-3

Summary of Example #1: As we can see in the result of example 1, there is B2 in Melbourne, and D2 is Delhi which is not matching, so the result is FALSE. Similarly, in B4 and D4, we have Delhi, which matches, so the result is TRUE. If we see the 5th Row where B2 have Goa and D2 has 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 that the result will be TRUE.

Compare Text in Excel – Example #2

How to Compare the column data in Excel that 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 the Home city and Preferred city, so apply the formula in the E column to compare.

Step 2: Click on the E2 cell, apply the EXACT function, select the B2 cell, apply the EXACT function, and select D2.

(= EXACT (B2, D2)

Compare Text Example 2-1.png

Step 3: Now click the Enter button to show the result based on the input data.

Compare Text Example 2-2

Step 4: Apply the above formula to all; we can drag it down on clicking the Plus sign of the E2 cell in the Excel sheet.

Compare Text Example 2-3

Summary of Example#2: As we can see in the result of the sheet, 2 examples in Excel, B2 is Melbourne, and D2 is Delhi which is not matching, so the result is FALSE. In B4 and D4, the same way as Delhi, which is matching, so the result is TRUE.

If we see the 5th Row where B2 have Goa and D2 has GOA, their case is different in both the cells, but the EXACT function will consider the case as case sensitive so that 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, we can use the 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 Excel, where a user wants to compare the data; the user wants to check Home City and Preferred City, so apply the formula in the E column to compare.

Step 2: Click on the E2 cell and apply the EXACT function with IF

Step 3: First, write the IF formula followed by EXACT, like =IF (EXACT (…))

Step 4: Select B2 in Text 1 and D2 cells in Text 2 and close the bracket.

Compare Text Example 3-1

Step 5: Now, it will ask for value_if_true and value_if_false; put the value for the same.

Compare Text Example 3-2

Step 6: Now give Match if the value is true and Differ if the value is false and close the bracket.

Value True or False

Step 7: Now click on the Enter button; the result will be shown based on the input data.

Input Data

Step 8: Apply the above formula to all; we can drag it down by clicking the Plus sign of the E2 cell.

Applying the formula

Summary of Example#3: As we can see in the sheet 3 examples, B2 is Melbourne, and D2 is Delhi which does not match, so the result is Differ. In B4 and D4, the same way as Delhi, which is matching, so the result is MATCH.

If we see the 5th Row where B2 has Goa and D2 has GOA, their case is different in both the cells, but the EXACT function will consider the case as case sensitive so that 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 to the output.

Things to Remember

  • Using the Equal sign in Excel for comparison will treat GOA as goa because the equal sign is case insensitive.
  • As the EXACT function is case sensitive, we can go for an equal sign when we are not bothered about the case.
  • We can compare the result inside the IF function to show a meaningful message or 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.

Quick Analysis Option

duplicate and unique value

This is how we can compare the data in the table for duplicate and unique values.

  • We can use the LEFT and RIGHT functions to compare only a part of the text.

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 used to Compare Text, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. TEXT Function in Excel
  2. Excel Separate text
  3. Compare Two Lists in Excel
  4. VBA Text
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
MS Excel & VBA for Data Science Course Bundle - 24 Courses in 1 | 10 Mock Tests
87+ Hours of HD Videos
24 Courses
10 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
All-in-One Financial Analyst Masters Training Program Bundle - 550+ Courses | 300+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.9
All-in-One Data Science Bundle - 400+ Courses | 550+ Mock Tests | 2000+ Hours | Lifetime |
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
4.7
Primary Sidebar
Popular Course in this category
MS Excel Course Bundle - 36 Courses in 1 | 13 Mock Tests
 137+ Hours of HD Videos
36 Courses
13 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.9
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download Text Compare Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Text Compare Excel Template

EDUCBA

डाउनलोड Text Compare Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more