Return Everything in Cell Except First Character

[Nanaia]

I'm trying to modify an existing formula to return cell results but exclude the first character. The data entry person adds a # to the beginning of a part number and my formula brings back the results like it needs to however for coding I need to remove the # from the part number. It's one of those 'I know there's a way' but I can't think how to incorporate it. I'm thinking something with LEFT but I'm not fluent enough to figure out how or where.

This formula works, I just need to find a way to exclude the first character of the results. The part numbers (without the #) are no longer than three numbers and letters.

This is my formula: [ =IFERROR(VLOOKUP(D37,Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"") ]

Thank you for your help.

 

[GlennUK]

Change D37 to MID(D37,2,999) - except I misread the question ... go with SUBSTITUTE as per Snakehips reply

 

[Snakehips]

Maybe try...
=IFERROR(SUBSTITUTE(VLOOKUP(D37,Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"'#",""),"")

Hope that helps.

 

[Nanaia]

[ =IFERROR(VLOOKUP(MID(D37,2,999),Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"") ] results in an empty cell.

[ =IFERROR(SUBSTITUTE(VLOOKUP(D37,Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"'#",""),"") ] provides the part number with the # at the beginning. So the same results as my original formula.

 

[Snakehips]

Sorry, my keyboard doesn't produce the # character within Excel so I coppied from your formula and got a stray ' in there!
Try.... =IFERROR(SUBSTITUTE(VLOOKUP(D37,Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"#",""),"")

 

[Nanaia]

Fabulous! The revised version worked! Thank you!

 

[Snakehips]

Brilliant. You are welcome.