Comparing strings is a fundamental task when working with data in spreadsheets. Whether you’re cleaning data, verifying entries, or creating dynamic reports, knowing how to perform a string compare in Google Sheets is essential. This guide will walk you through practical examples of comparing strings in Google Sheets, demonstrating both case-sensitive and case-insensitive methods using simple formulas. We’ll show you how to use these techniques effectively with clear, step-by-step instructions and examples.
Case-Sensitive String Comparison in Google Sheets
For situations where you need to ensure an exact match, including capitalization, Google Sheets offers a straightforward method using the EXACT function. This method of string compare in Google Sheets is case-sensitive, meaning it distinguishes between uppercase and lowercase letters.
The formula is as follows:
=EXACT(A2, B2)In this formula, A2 and B2 represent the cells containing the strings you want to compare. The EXACT function will return TRUE if the strings in both cells are identical, character for character, including case. If there’s any difference, it will return FALSE.
Let’s illustrate with an example. Imagine you have two columns of data in your Google Sheet, Column A and Column B, and you want to compare the strings in each row.
To perform a case-sensitive string compare in Google Sheets between columns A and B, starting from row 2, you would enter the following formula in cell C2:
=EXACT(A2, B2)Then, you can easily apply this formula to the rest of the rows in column C by dragging the fill handle (the small square at the bottom right of the selected cell) down.
As you can see from the example, the EXACT function accurately identifies strings that are precisely the same, like “lion” and “lion” returning TRUE. However, it correctly distinguishes between “Panda” and “panda”, returning FALSE because of the case difference, demonstrating a precise string compare in Google Sheets.
Case-Insensitive String Comparison in Google Sheets
Sometimes, you need to compare strings regardless of whether the letters are uppercase or lowercase. For a case-insensitive string compare in Google Sheets, you can combine the EXACT function with the UPPER function (or LOWER function, the concept is the same). This approach first converts both strings to the same case before comparing them.
Here’s the formula for a case-insensitive string compare in Google Sheets using UPPER:
=EXACT(UPPER(A2), UPPER(B2))In this formula, UPPER(A2) converts the string in cell A2 to uppercase, and UPPER(B2) does the same for cell B2. Then, the EXACT function compares these uppercase versions of the strings. This effectively ignores the original casing of the strings.
Using the same data example, to perform a case-insensitive string compare in Google Sheets, you would enter the following formula in cell C2:
=EXACT(UPPER(A2), UPPER(B2))Drag the fill handle down to apply this formula to the remaining rows in column C.
In this case, the formula returns TRUE for “Panda” and “panda” because, after converting both to uppercase (“PANDA” and “PANDA”), they are considered identical. This method allows for a flexible string compare in Google Sheets, focusing on the characters themselves rather than their case.
The UPPER function is used here to convert strings to uppercase, but you could similarly use the LOWER function to convert them to lowercase and achieve the same case-insensitive comparison. The key is to ensure both strings are converted to the same case before using the EXACT function to compare them.
By understanding these two methods, you can efficiently perform both case-sensitive and case-insensitive string compare operations in Google Sheets, making your data manipulation and analysis tasks more effective.
