text / format issues

I get a file from HQ (see attached). I'm trying to do a relatively simple vlookup, but it fails because there's a single space in front of a text string.

eg: the file has " 123abc" ...not "123abc" ...but trying to duplicate it with
=vlookup(" "&"123abc", range, cols, false) doesn't work!

Please take a look at the cell formatting, i'm trying to use TEXT() or something to do this vlookup. I can't change the HQ file.

thanks folks!