Cannot solve vlookup/mid function problem...

Hello all,

I built a spreadsheet to automatically decode part numbers and can't get a few specific values to show up. I have no idea why. The problem lies in between a mid function and the lookup. The mid function for some reason results in an output that breaks the lookup and returns #N/A. The intersting part is that I use the same formula througout the whole sheet and 70% of them work. The other 30%...I have no idea.

I think it is a formatting issue, as when i write over the mid function with a number it works. However, I can't use "+0" or similar tricks because the result can be either a number or letter. I've tried every way I can think of to reformat but am having no luck.

I've attached the file for reference. You can see that in the "Flash Decode" sheet, cells C5, C9, and C11 return #N/A but the others cells work fine. I just can't get the mid function hin these cells to return a usuable value. Also, the problem cells work fine if the mid function returns a letter, just not with numbers.

Any help is much appreciated! I feel like I need to break things

P.S. attachment should work now