How to Compare Text in Excel: 8 Best and Effortless Methods

If you’re dealing with a huge database in an Excel spreadsheet and need to cleanse the database for duplicate entries, you can make use of the following techniques to compare text in Excel.

Excel spreadsheet isn’t only to maintain financial accounts, address books, or text databases. It can also help you to analyze the data to erase duplicate entries. Additionally, you can use some easy formulas and logical functions of the Excel worksheet to compare cells and cell ranges to find out the specific values you’re looking for.

Though you can visually scrape through the entire worksheet for duplicate or target values, it may not be the ideal method when the Excel worksheet contains thousands of entries. Furthermore, its various Conditional Formatting functions allow you to automatically highlight cells when it detects the value you’re looking for.

Read on to learn how to compare text in Excel. You’ll also find out how Excel compares text in two columns using Conditional Formatting. Let’s dig in!

Why Do You Need to Learn Compare Text in Excel?

There could be infinite reasons to perform a compare text in Excel function. However, the followings are the most frequent ones that you might face in your personal and professional life:

• You run a business and you created separate Excel databases for customers from all 50 states of the USA. Now, someone mixed the customer names. Here, instead of manually scrubbing the worksheet, you can use compare text in Excel to find customer names that got mixed up between 50 states.
• Your business has two separate databases for blacklisted and whitelisted addresses for merchandise delivery from your eCommerce store. An employee mixed up the addresses. In this awful situation, you can use the Excel string compare function.
• You created a grocery shopping list for the whole month and mixed up the list with some groceries that you got last month. Here, you can utilize compare text in Excel.
• If you’re a high school teacher and need to evaluate student responses against questions automatically, you can use Excel string to compare columns and rows.
• For business promotion purposes, you’re giving away discount codes to thousands of customers. Now, you want to retarget the customers who used the discount codes to pitch premium products and subscriptions. You can use Excel compare text in two columns function to match customer names who used promo codes from the master database of customers to whom you sent the promo emails.

There could be thousands of other reasons to compare values and texts in Excel. Whatever the reason is, you’ll find the following eight methods for Excel compare text in two columns useful:

1. Compare Text in Excel Using the Equals Operator

For example, you just need to compare two columns of data to ensure you got all the supplies you need. Here, you can match the requested supplies with the delivered ones using the case-insensitive comparison operator Equals.

Thus, you can copy the reference data from one app and paste that under the reference or requested supplies column. Then, you can import the target data from another app and paste the data under the target or supplies received column.

You don’t need to think about matching the cases and values of the cell texts though you’re importing data from different apps. Here’s how the case-insensitive comparison works:

• Copy and paste the following formula in the cell where you want the comparison result:

=A2=B2

• Hit Enter and Excel will show either True or False depending on the reference and target data.

• If the formula looks good, copy and paste it down the whole column to get an instant idea about the goods received vs goods ordered.

2. Use the EXACT Function to Compare Text in Excel

Let’s consider you’re working on a project where case sensitivity is an important consideration when matching values between columns in an Excel worksheet. In this scenario, you can’t use the Equals operator to compare text in Excel.

In this delicate scenario, you can use the EXACT formula as shown below:

• Create an appropriate column header and enter the reference data in the rows below the column.
• Similarly, create the data to be evaluated column beside the first column and populate data in the rows below.

• Once done, put an equals sign (=) in the cell where you want a comparison result, TRUE or Fales, and type the following formula:

=EXACT(B2,C2) (for same worksheet)

=EXACT(Sheet8!B2,Sheet9!C2) (for different worksheets)

• You can also select the reference or target data from another worksheet of the same Excel workbook.
• Hit Enter and you get a quick comparison based on the upper case and lower case of the given texts.

3. Compare Text in Excel Using the IF Formula

Let’s consider you want to use either the EXACT formula or the Equals operator to compare text but you don’t want to see TRUE or FALSE as the comparison result. In fact, you want to show texts like Matched and Not Matched. In that case, you can nest any of the above formulas inside an IF function as outlined below:

• Enter an equals sign (=) and begin the formula with IF.
• Then, enter any of the above formulas as the logical challenge of the IF function.
• After the logical challenge, enter the text to be shown, like Matched within quotation marks.

• Put a comma and then put another text you want Excel should display if the logical challenge fails. In this case, Not Matched is surrounded by quotes.
• Thus, the final formula becomes as shown below:

=IF(EXACT(B2,C2),“Matched”,“Not Matched”)

4. Use VLOOKUP to Compare Text in Excel

For example, you need to find the names of shortlisted candidates from Group A. Here, you must match the names in Group A with the names in the column of all shortlisted candidates.

Here, you can easily use the VLOOKUP function to let Excel compare text in two columns. Here’s how it’s done:

• The first column of the worksheet should contain the names from Group A.
• In the next column, you can copy and paste the names of shortlisted candidates.

• In the outermost column, you can get the comparison results which will show the name of the candidates who got qualified in the first round.
• Now, simply copy and paste the following formula where you want a comparison list of Group A names from the Shortlisted Names.

=VLOOKUP(A2,$C$2:$C$15,1,FALSE)

• To replace the #N/A errors with the Failed text, use this formula mentioned below:

=IFNA(VLOOKUP(A2,$C$2:$C$15,1,FALSE),“Failed”)

5. Compare Text to Find Absent Attendees Quickly

Let’s consider, you’re the event coordinator for a tech conference. You must match the attendees against a list of registered persons to send certificates only to the individuals who showed up for the seminar. Here’s how you can do it using VLOOKUP and IFNA formula where you’re comparing text between two columns:

• Create a column header for Registered and put the names in the below rows.
• To the right of this column, create another column for the individuals who actually attended the seminar.

• In the third column, enter the following formula:

=IFNA(VLOOKUP(A2,$C$2:$C$15,1,FALSE),“Absent”)

• Copy and paste the formula downwards to get the present or absent status of the registered individuals.

Excel will match Registered names from the reference column to show results in a third column where you want the names. Also, the IFNA formula will ensure if a #N/A error shows up, Excel replaces the error code with the text Absent.

6. Compare Text in Excel for Partial Values

Let’s say you need to compare texts between Columns A and B, but you’re not looking for an absolute match. In this situation, you must use the FIND formula as outlined below:

• Put the reference text data under Column A and the target data in Column B.

• In Column C, enter the following formula to find and show a value for partial text comparison in Excel:

=FIND(C2,A2)

• The FIND function returns an error where it can’t find a partial match. I nested the FIND function inside the ISNUMBER function. Now the formula will show TRUE when there’s a partial match and FALSE when no match at all.

=ISNUMBER(FIND(C2,A2))

7. Compare Texts or Strings Using Conditional Formatting

Consider you’re in an office where you’re in charge to review all purchases. You get different purchase requirements from various departments of the business. It’s your duty to ensure your organization isn’t submitting purchase orders for duplicate items.

In this scenario, Excel’s Conditional Formatting can compare two or more columns of data and highlight cells that contain duplicate values. Follow these steps to practice this compare text trick in Excel:

• Compile a database of texts that you want to compare.
• Select the whole range and click Conditional Formatting on the Home menu of the Excel ribbon.

• On the drop-down list, hover the mouse cursor over the Highlight Cell Rules option.

• Multiple cell rules will show up. Select Duplicate Values.
• On the Duplicate Values pop-up, you can select the highlighting format by clicking the Values With drop-down list.

8. Compare Excel Columns if Text 1 Is Inside Text 2

If you need to find a set of texts within a group of texts, you can use the SEARCH function as shown here:

• Create a column of texts for reference and another column for test texts.
• Now, enter the following formula in the third column:

=SEARCH(C2,A2)

• Hit Enter. Now, copy and paste the formula in the cells of the third column.

• Press Enter to get more results.

Excel will show the character number from where it finds a match for the test text on the reference column.

Conclusion

There could be many other formulas and nested formulas to match texts between two or more cells or columns in Excel. But the easiest and most widely used methods are the ones mentioned above in this article.

If you know any other intuitive way to compare text in Excel, don’t forget to mention the method in the comment box below. In the meantime, you can learn how to use Excel’s IF-THEN formula.