Excel users, beware, comparing text in formulas doesn’t always behave as you’d expect. While most programming languages treat text strings with upper/lower case as different, Excel doesn’t. By default, it compares text without case sensitivity, leading to potential errors in formulas and automation. Learn how to correctly use functions like EXACT() and TRIM() to avoid subtle but frustrating mistakes in your spreadsheets.
When Excel compares two strings, it’s automatically case INsensitive. In other words, all these return TRUE
=“delivered”=”DELIVERED”=“Delivered”=”DELIVERED”=“deLIVered”=”DElivERED”
In most computer languages that does NOT happen. Any case variation in the text makes a comparison return FALSE, but not in Excel formulas. Even Office VBA treats “delivered” as different from “Delivered”.
That Excel peculiarity can be useful for quick case insensitive text comparisons.
Any variation of the text will change the result, this formula returns FALSE
=“DELIVERED ”=”DELIVERED”Why? There’s a space at the end of the first word. That’s almost invisible when looking at the cell or formula bar but Excel knows the difference unless you use TRIM() to remove any extra spaces before and after a string.
Case Sensitive text comparison
In Excel formulas to differentiate between uppercase and lowercase letters, use the IF function along with the case-sensitive EXACT function.
For example, to return “Yes” only when the cell contains “DELIVERED” (in uppercase), use the following formula:
=IF(EXACT(C1,"DELIVERED"), "No", "Yes")
Excel’s better text splitting powers
Add Singular or Plural text in Excel
IF and Nested IF Statements in Excel
Singular / Plural text in Excel
Excel’s text and number alignment choices
The hidden trap in Excel’s DateDif()
The big difference between what Excel shows and what Excel knows
