Microsoft

Office solution: Why an IF() might return an unexpected result

This week, learn about string comparison from the discussion that followed last week's Office challenge: When an IF() returns FALSE, when it should return TRUE.

Last week's challenge was great! I want to thank everyone who took part. Charles Bundy was busy dropping hints during the first day or two. Alopezlujan was the first to expose the culprit. Alopezlujan suggested using LEN() to determine the difference, which you can see below.

The LEN() functions in column C return the number of characters in the values in column A. The functions in column D evaluate the values in column B in the same way. While LEN() exposes an additional character in B4, it's unclear what that character is. In edit mode, you can see that there is an invisible character following C. If you edit the cell by removing the character, the IF() function returns TRUE and the LEN() function returns 1.

Sometimes, users inadvertently enter unnecessary space characters during input. Sometimes, you import these problems from other sources. When that's the case, you might have a sheet full of values that contain unnecessary space characters.

Damiross was the first to mention using TRIM() as a fix. This function removes leading and trailing space characters. Damiross' formula, =IF(TRIM(A4)=TRIM(B4),TRUE,FALSE) works great. If there's any doubt about the validity of your values, don't hesitate to use TRIM() in your formulas.

Techrepublic and Jbenton offered an alternative, EXACT(). This function compares two text strings and returns TRUE when they're the same, FALSE when they're not. It works only with string values, so it won't work in every instance.

David.hanshumaker mentioned the CLEAN() function, which removes all nonprintable characters in the same way TRIM() removes space characters. This function is invaluable when working with foreign data that sometimes drags along all kinds of phantom characters that you can't see.

This challenge resulted in an informative study of string comparison solutions. Thanks everyone for contributing to another great challenge!

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
dogknees
dogknees

There is some benefit to the way comparisons work. Rather than a problem, the "wrong" values can alert you to something amiss in the data. Using a TRIM or whatever other modification so "similar" strings match sort of removes this usage. I guess I often use it as an initial filter to see if the data is clean.

ssharkins
ssharkins

You certainly can use the errors to compare -- suppose it just depends on your needs. Thanks!