Compare Two Strings in Excel/Google Sheets

 Compare Two Strings in Excel/Google Sheets

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
 Compare Two Strings in Excel/Google Sheets

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")
 Compare Two Strings in Excel/Google Sheets

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
 Compare Two Strings in Excel/Google Sheets

Video: Compare Two String in Excel/Google Sheets

Related Posts

Sort Data by Last Character in google sheets

Sort Data by Last Character in google sheets If you are an avid Google Sheets user, then you may have an urgent task to rank the text value by the last character from the cells; you can do this task ...

Rank Data with Multiple Criteria in google sheets

If you are an enthusiastic Google Sheets user, then you may have an urgent task to rank a specified datelist based on given multiple criteria; you can easily perform this task manually, which is also feasible, but it only works ...

Filter with Exact Match(Case-Sensitive)

Suppose that you have a table consisting of a few cells with few values, and you want to filter out the set of records with the exact match concerning case sensitivity. You might take it easy and would prefer to ...

Case Sensitive Lookup with SUMPRODUCT and EXACT

Today, we will show you how to use SUMPRODUCT and EXACT to perform a case sensitive exact match. In this article, we provide a simple example to calculate bonus for employees whose names are case-sensitive. If you meet similar scenarios ...

Basic Usage of INDEX & MATCH – Case Sensitive Lookup

In Excel, INDEX function and MATCH function are often used together for retrieving data from a particular position. MATCH function is one of Excel lookup & reference functions that can perform approximate match or exact match by setting different match ...

Sidebar