Be careful comparing text in Excel because it looks easy but there are traps when comparing exact text (including upper/lower case) or not. Whether you’re cleaning data or ensuring accuracy in sensitive fields like usernames or product codes, we’ll help you get the results you need every time.
Case-insensitive IF statement for text values
When Excel compares two strings, it’s automatically case INsensitive. In other words, all these return TRUE.
“delivered”=”DELIVERED”“Delivered”=”DELIVERED”“deLIVered”=”DElivERED”While Excel formulas like IF, VLOOKUP, and MATCH are great for quick comparisons, they often ignore case differences by default.
Not what you might expect
Programmers might be surprised by the way Excel works because most coding languages would return FALSE for all the above comparisons.
A standard text string comparison has to be a precise match including upper/lower case. Excel mostly defaults to a case INsenstive test.
Beware hidden differences
But any variation of the text will change the result, this formula returns FALSE
“DELIVERED ”=”DELIVERED”
Why? There’s a space in one of the strings. That’s invisible when looking at the cell or formula bar but Excel knows the difference.
Case Sensitive text comparison
To differentiate between uppercase and lowercase letters, use the IF function along with the case-sensitive EXACT function.
For example, to return “No” only when B2 contains “DELIVERED” (in uppercase), use the following formula:
=IF(EXACT(B2,"DELIVERED"), "No", "Yes")
Now, simply drag the mouse from C2 through C10 to apply the formula and display the required action.
For comparison, here’s the same table without EXACT, rows 2, 5 and 9 now have different results.
