Cleaning imported data
LEN function: count characters and expose hidden trailing spaces
Measure exact string length—including spaces and punctuation—to diagnose why two visually identical bin codes fail to match.
Two bin labels that look identical on screen
In a narrow column, "BAY-04 " in A2 (with one trailing space) looks indistinguishable from "BAY-04" in A3, yet A2=A3 evaluates to FALSE and exact lookups fail.
| Cell | Stored text | Character count |
|---|---|---|
| A2 | BAY-04 (trailing space) | 7 |
| A3 | BAY-04 | 6 |
| A4 | (empty) | 0 |
Measure every character including spaces
=LEN(A2) returns 7 while =LEN(A3) returns 6, immediately pinpointing the unseen character. Use =IF(A4="", 0, LEN(A4)) when rows may be unpopulated so blank cells always report 0.
=LEN(A2)Count removable spaces before cleaning
Subtracting the trimmed length—LEN(A2) - LEN(TRIM(A2))—reports how many leading, trailing, or repeated ASCII spaces would be removed (1 for A2, 0 for A3). Remove the trailing space in A2 in the practice grid to verify LEN drops to 6 and A2=A3 turns TRUE.
=LEN(A2)-LEN(TRIM(A2))Try the example
Use the original values or change them to test your understanding.