This article will talk about how to compare two given strings in a Microsoft Excel spreadsheet or Google Sheets. How to compare two strings in Excel by using VBA macros to see if they are the same, if they are the same then return TRUE, if not then return FALSE.

Suppose there is a worksheet with two columns A,B containing text values, if you want to compare the text values of two peers in columns A,B to see if they are the same, how do you do the comparison in Excel or Google Sheets? Below we will compare text values by formula and VBA code.

Method1: Compare Strings Through Excel/Google Sheets Formula

In Microsoft Excel Spreadsheet or Google sheets, you can use the EXACT formula to compare two strings. When two text values are the same, the EXACT function will return TRUE, otherwise it will return FALSE.

Generic Formula

=EXCEC(TextValue1,TextValue2)

For the example in this article, we can use the following EXACT formula:

=EXACT(A2:B2)

You need to enter the above EXACT formula in cell C3, after that you can drag the auto-fill handle from cell C3 to cell C7 so that the other cells can also apply the above EXACT formula.

Explanation

EXACT functions are case-sensitive by default, for example.” Excel” and “EXCEL” are two different text values. If you want to compare two text strings in a case-insensitive manner, then you can simply use the “=” operator.

The formula is as follows:

=A3=B3

For case-sensitive cases, we can use the EXACT function to compare two strings.

Since EXACT returns TRUE or FALSE results by default, if you want to have a more friendly output, then you can embed the EXACT function into the IF function so you can define different output results.

The formula is as follows.

=IF(EXACT(A3,B3),"equal","not equal")

Method2: Compare Strings Through VBA Macro

When you want to compare strings, if you want to compare strings to get the result of which string is smaller or larger than the other, which is impossible to achieve through the EXACT function, then how to solve this problem? This problem can be perfectly solved by the StrComp() method in Excel VBA, which can compare two strings and get the desired value of the result.

• If the two strings you want to compare are equal, this method will return 0.
• If the first string is smaller than the other, you will get -1 as the return value.
• If the first input string is greater than the other string, you will get the value 1 as the result.
• If one of the strings entered by the user is null, the return value will be Null.
• If you want to compare “excel” and “Excel”, then you can execute the following VBA code:

Sub comparetest()

Dim compare As Integer

string1 = "Excel"

string2 = "excel"

comparestring = StrComp(string1, string2)

MsgBox comparestring

End Sub

Video: Compare Two String in Excel/Google Sheets