When using a Microsoft Office Excel spreadsheet processor, in addition to comparing numerical values, it is often necessary to compare text ("string") data of table cells. This can be done using the built-in Excel comparison functions if the result of the operation is to be obtained in the form of a numerical or logical value. Alternatively, you can use the conditional formatting options if the result is to visually highlight matching (or non-matching) table cells.
Instructions
Step 1
Use the built-in COUNTIF cell comparison function if you want to compare text values in table column cells with sample text and recalculate all that match. Start by filling a column with text values, and then in another column, click the cell where you want to see the counting result and enter the appropriate formula. For example, if the checked values are in column A, and the result should be placed in the first cell of column C, then its content should be as follows: = COUNTIF ($ A: $ A; "Grape") Here "Grape" is a string value with which the values of all cells of column A are compared. You can skip specifying it in the formula, but place it in a separate cell (for example, in B1) and insert the corresponding link into the formula: = COUNTIF ($ A: $ A; B1)
Step 2
Use the conditional formatting options if you need to visually highlight the result of comparing string variables in the table. For example, if you need to select cells in column A, the text of which matches the pattern in cell B1, then start by selecting this column - click its heading. Then click the Conditional Formatting button in the Styles command group on the Home tab on the Excel menu. Go to the "Cell Selection Rules" section and select the "Equals" line. In the window that opens, specify a sample cell (click cell B1) and select an option for matching rows from the drop-down list. Then click the "OK" button.
Step 3
Use a combination of the built-in IF and CONCATENATE functions when you need to match more than one text cell to a pattern. The CONCATENATE function concatenates the specified values into one string variable. For example, the command CONCATE (A1; "and"; B1) will add the text "and" to the row from cell A1, and after it will place the row from cell B1. The string constructed in this way can then be matched against the pattern using the IF function. When you need to compare more than one string, it is more convenient to give your own name to the sample cell. To do this, click it and to the left of the formula bar, instead of a cell designator (for example, C1), type its new name (for example, "sample"). Then click the cell in which the result of the comparison should be and enter the formula: IF (CONCATENATE (A1; "and"; B1) = sample; 1; 0) Here unit is the value that the cell with the formula will contain if the comparison will give a positive result and zero for a negative result. It is very easy to multiply this formula for all table rows that need to be compared with the sample - move the cursor to the lower right corner of the cell and, when the cursor changes (turns into a black cross), press the left mouse button and drag this cell down to the last compared row.
