You've been there... Trying to do a VLOOKUP and it just doesn't want to work. I've been in the habbit of using =TEXT(TRIM(A1),0) whenever I do a VLOOKUP using employee numbers, because in so many of our reports, sometimes these IDs are numbers, sometimes they're text, and sometimes their both. Just making the TEXT and TRIM functions a habit has saved me some headache.
Today, I ran into the situation where this still didn't resolve the issue. For whatever reason, this new system we have has been designed to add trailing characters to IDs. They looks like spaces, but they aren't, and TRIM doesn't clean them up. They're actually the CHAR(160) symbol.
Anyway, this formula is likely overkill, but sometimes overkill is just enough. This formula combines several different text cleanup functions: