How to Compare Text in Excel Spreadsheet Cells

Often, users of the Excel spreadsheet editor face such a task as comparing text in the cells of a table. There are several ways to implement this procedure. In the article, we will consider in detail each of the methods that allows you to implement a comparison of textual information in the cells of the plate.

Comparing two cells in a table editor

The spreadsheet editor provides two different methods for comparing textual information in cells. The methods differ from each other in that one is case-sensitive and the other is not.

Applying a special formula without case sensitivity

The simplest case-insensitive comparison of text in 2 cells is to use the “=” symbol between cell coordinates. Formula example: =A1=V1.

Important! A1 and B1 are elements of comparison. As a result, in the cell with the formula, either the value FALSE or the value TRUE will be displayed.

If the user wants to display unique messages as a result, indicating the presence of matches in the cells, then additionally, the “IF” operator must be added to the formula. A simple example of such a formula: =IF(A2=B2, Matches, Does NOT match).

The picture below clearly shows how the two considered formulas cope with the procedure for comparing text information in cells.

How to Compare Text in Excel Spreadsheet Cells
1

Applying a custom formula is case-sensitive

Often when working with documents there are such moments when it is necessary not only to compare textual information, but also to take into account the case. To implement this simple procedure, a special operator “SOVPAD” is used. In the English variation, it has the name “EXACT”. The general form of the formula looks like this: =EXACT(text1, text2). The text1 and text2 arguments are the coordinates of the cells in which the textual information will be compared.

For example, the data for comparison is located in the fields with the coordinates A1 and B1. In this case, the applied formula would look like this: =COINCIDENCE(A1; V1). TRUE will be displayed if the case of each character is the same. Otherwise, FALSE will be displayed.

 Attention! If the user wants to display unique messages as a result, indicating the presence of matches in the cells, then additionally, the “IF” operator must be added to the formula.

A simple example of such a formula: =IF(EXACT(A1, B1), “Match”, “NOT match”). The picture below clearly shows how the two considered formulas cope with the procedure for comparing text information in cells with case sensitivity.

How to Compare Text in Excel Spreadsheet Cells
2

Comparing multiple cells in a table editor

Now let’s talk in detail about how to compare textual information in more than two cells. These methods are almost identical to those discussed above.

Applying a custom formula that is case-insensitive to multiple cells

Here, as in the previously discussed methods, two variations of the formula are used:

  1. The first version of the formula looks like this: =I(A1=V1; A1=S1).
  2. The second version of the formula looks like this: =IF(AND(A1=B1, A1=C1), Match, NOT match).

The “AND” function implements the return of the TRUE value in the event that all the fields of the table have the same values. FALSE is returned if any of the values ​​do not match. The “IF” operator allows you to display on the screen those indicators that the user entered into the formula.

The picture below clearly shows how the two considered formulas cope with the procedure for comparing not only textual information, but also with numerical indicators, as well as with data in the “Date” format.

How to Compare Text in Excel Spreadsheet Cells
3

Apply a special case-sensitive formula to multiple cells

Here, as in the previously discussed methods, two variations of the formula are used:

  1. The first version of the formula looks like this: =I(COINCIDENCE(A1; V1); COINCIDENCE(A1; S2)).
  2. The second version of the formula looks like this: =IF(AND(EXACT(A1, B1), EXACT(A1, C1)), Exact Match, NOT Match).

As in the previous example, the “AND” function implements the return of the TRUE value in the event that all the fields of the plate have the same values. FALSE is returned if any of the values ​​do not match. The “IF” operator allows you to display on the screen those indicators that the user entered into the formula. The result of the transformation looks like this:

How to Compare Text in Excel Spreadsheet Cells
4

If the user needs the formula to display the presence of matches of at least 2 indicators, then he needs to use the following formula: =IF(OR(EXACT(A1, B1), EXACT(A1, C1), EXACT(B1, C1)), At least two exact matches, No matches. In other words, in this case, the presence of at least 2 matches in 3 cases will output a positive value. The result of the transformation looks like this:

How to Compare Text in Excel Spreadsheet Cells
5

If the user needs to make a comparison in a table in which there is a huge number of cells, then the above formula will not be convenient to use, as it will become too long. Here it is more expedient to use the mathematical operator “COUNTIF”. You can count the number of matches in a large range of cells using the following formula: {=COUNTIF(A2:D2,A2:D2)}. The result of the transformation looks like this:

How to Compare Text in Excel Spreadsheet Cells
6

It is worth noting! It is very important to keep track of the number of unnecessary spaces in the cells. The fact is that if there is a space in the cell, then it will be perceived by the table editor as a symbol. Therefore, before using formulas, you need to make sure that there are no unnecessary spaces in the cells.

Conclusion

We found out that in the Excel spreadsheet editor there are a huge number of methods that allow you to compare text information in cells. We learned that you can compare information case-sensitive and case-insensitive. To implement this procedure, an operator such as “SOVPAD” is excellent. Additionally, for more complex procedures, functions such as “AND”, “OR”, “IF” and “COUNTIF” are used.

Leave a Reply