How to properly use Excel String Compare function?

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.

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.

Complete 2019 Excel for Beginners in video

Comments (0)

Leave a comment