output something like "All match" and "Not all match" instead of TRUE and FALSE, use the IF function like we did in the previous examples: To make the results more user-friendly, i.e. The number of cells containing the same value as in the sample cell (returned by the COUNTIF function).Īssuming the sample text is in C2 and the strings to compare are in the range A2:B6, the formula goes as follows:.The total number of cells in a specified range (the number of rows multiplied by the number of columns), and.In the logical test of the IF function, you compare two numbers: ROWS( range)*COLUMNS( range)=COUNTIF( range, sample cell) If the character case does not really matter, you can use the following formula to compare cells to a sample: Case-insensitive formula to compare cells to a sample text The following examples show how you can verify that all cells in a given range contain the same text as in a sample cell. Like in the previous example, the first formula delivers TRUE and FALSE values, whereas the second one displays your own texts for matches and differences:Ĭompare a range of cells to a sample cell To compare multiple strings to each other to see if they match exactly, use the following formulas: The IF formula outputs the labels that you type in it, " Equal" and " Not equal" in this example.Īs demonstrated in the screenshot below, the formula works perfectly with any data types - text, dates and numeric values:Ĭase-sensitive formula to compare text in several cells The AND formula returns TRUE if all of the cells contain the same value, FALSE if any value is different. Case-insensitive formula to compare more than 2 cellsĭepending on how you want to display the results, utilize one of the following formulas: To compare more than 2 cells in a row, use the formulas discussed in the above examples in combination with the AND operator. The following screenshot shows the results of the case-sensitive string comparison in Excel: =IF(EXACT(A2 ,B2), "Exactly equal", "Not equal") If you want the EXACT function to deliver some other results, embed it in an IF formula and type your own text for value_if_true and value_if_false arguments: Where text1 and text2 are the two cells you are comparing.Īssuming your strings are in cells A2 and B2, the formula goes as follows:Īs the result, you get TRUE for text strings match exactly including the case of each character, FALSE otherwise. Case-sensitive text comparison can be done using the Excel EXACT function: In some situations, it may be important not only to compare text values of two cells, but also to compare the character case. For example:Īs you see in the screenshot below, both formulas compare text strings, dates and numbers equally well:Ĭase-sensitive formula to compare strings in Excel If you want to output your own texts for matches and differences, embed the above statement in the logical test of the IF function. The result of the formula are Boolean values TRUE and FALSE. Where A1 and B1 are the cells you are comparing. To compare two cells in Excel ignoring case, use a simple formula like this: Case-insensitive formula to compare 2 cells There are two different ways to compare strings in Excel depending on whether you seek case-sensitive or case-insensitive comparison.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |