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!
