Return Everything in Cell Except First Character

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
227
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
Change D37 to MID(D37,2,999) - except I misread the question ... go with SUBSTITUTE as per Snakehips reply
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Maybe try...
=IFERROR(SUBSTITUTE(VLOOKUP(D37,Table2[[#All],[LINE '#]:[QUANTITY]],2,FALSE),"'#",""),"")

Hope that helps.
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
227
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
[ =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

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Jan 11, 2018
Messages
227
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Fabulous! The revised version worked! Thank you!
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,059
Office Version
  1. 2013
Platform
  1. Windows
Brilliant. You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,649
Messages
5,549,179
Members
410,903
Latest member
natesreich
Top