I often get this problem. A long list contains partnumbers (some numerical,
some text) and I need to get price from a pricelist. Even though it is an
exact match I get #N/A in return. Only way to get it working is to edit the
source cell (F2+Enter).
I have tried TRIM() and VALUE() but since the list contains both types I get
#VALUE in return for all text cells.

Is there a way to "transform" source column so that it will work 100%?