How to properly use Excel String Compare function?

Using MS Excel as an everyday solution, can make your life much easier. It doesn't matter where you work, the way that Excel works, it will get you out of trouble, with plenty of useful simple functions to count number of occurrences, find the position of a character in a string, count digits in cell or count characters in cell for example.


Properly use Excel string compare function

Using MS Excel as an everyday solution, can make your life much easier. It doesn't matter where you work, the way that Excel works, it will get you out of trouble, with plenty of useful simple functions to count number of occurrences, find the position of a character in a string,  count digits in cell   or  count characters in cell   for example.

But Excel has an interesting feature that is much easier to work with than any other software nowadays. It will let you compare two strings and see if they are similar or different from one another.

#1 How do you compare two strings for similarity in Excel?

If you want to determine if two cells are equal, which is a much easier mathematical version, you will use the EXACT function.

It doesn't matter if there are numbers or words or phrases. This function will examine the cells for which you want to perform a proper Excel String compare.

All you have to do is to click on the cell you want to get the result. After that, click on the formula tab, which is above the cells. On the formula tab you will write the function you will need to work with.

It's not necessary to look up for the functions in the function settings. You can learn few of them so the process will be faster.

On that tab you will put the equal sign before any function. So it will be like this:

Excel String compare case sensitive: =EXACT

E.g. Let's say C1 is 'watermelon' and C5 is 'Watermelon'.

The function to perform the exact Excel string compare will be:

Exact case sensitive Excel string compare: =EXACT(C1,C5)

On the cell you chose to get the result, Exact will turn into TRUE or FALSE, depending on what you want to compare.

In our case it will be a FALSE result, because the case is different, and the function EXACT is case sensitive. If you want to compare 'Green' and 'Green', the software will know that the strings are the same and you will get a TRUE result.

Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

You may have a question - what to do in excel if the strings are equal. Quite often, Excel worksheets contain duplicate records that are duplicated many times. But repetition does not always indicate a data entry error. Sometimes repeated entries with the same values ​​were made intentionally. Then the problem may arise when processing, searching for data or analyzing in such a table.

To make it easier for you to work with such tables, we recommend that you automatically merge the same rows in an Excel table by highlighting them in color. Here's a simple but working tip.

Either way, you don't have to worry about the result because the Excel software will always get it right. Just take care you use the function you need for the process that you want to perform, in our case an exact Excel string compare between two cells.

#2 Compare two strings with personalized return value

You can even customize the answers you will get on that cell. Instead of getting TRUE or FLASE, you can get YES or NO. But for that you need an additional function, which is the IF function.

Compare two string with custom return value: =IF(EXACT(C1,C5), "YES", "NO" )

Also, don't forget to use the quotation marks when you are not using a function or cells numbers.

#3 How to compare two strings case insensitive in Excel?

However, the EXACT function is a case sensitive and it exists a much simple way. Forgetting about this function you can also use the equal sign as a pure function.

Excel string compare case insensitive: =C1=C5

It is not hard to compare cells between them without paying attention to lowercase or uppercase. It doesn't matter if you are writing WaTErmelon or simply watermelon because this MS tool will verify the content of the string and it will get the result right.

In short, how to perform a perfect Excel String compare

Of course, there are many ways to compare two strings in MS Excel. But it does require to know a little bit of coding. And not anybody has time to learn the coding way when using Excel for work. So, in the end, this is a proper way to get a proper result for your needs.

Excel string compare case sensitive: =EXACT(C1,C5)
Excel string compare case insensitive: =C1=C5

Go even further with MSExcel string operations like count number of occurrences, find the position of a character in a string, count digits in cell, or  count characters in cell   now that you master the string comparison in your favourite spreadsheet program, MSExcel.

Frequently Asked Questions

What method should be employed to accurately compare two strings in Excel, allowing for case-sensitive or case-insensitive comparisons?
Use the `EXACT(text1, text2)` function for case-sensitive comparisons, which returns TRUE if the strings are exactly the same, including the case, and FALSE otherwise. For case-insensitive comparisons, use the `=text1=text2` syntax, which evaluates to TRUE if the strings match, disregarding case.

Complete 2019 Excel for Beginners in video


Become an Excel Pro: Join Our Course!

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.

Enroll Here

Elevate your skills from novice to hero with our Excel 365 Basics course, designed to make you proficient in just a few sessions.



Comments (0)

Leave a comment