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.
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.
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:
- The first version of the formula looks like this: =I(A1=V1; A1=S1).
- 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.
Apply a special case-sensitive formula to multiple cells
Here, as in the previously discussed methods, two variations of the formula are used:
- The first version of the formula looks like this: =I(COINCIDENCE(A1; V1); COINCIDENCE(A1; S2)).
- 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:
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:
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:
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.