# Return Everything in Cell Except First Character

#### Nanaia

##### Board Regular
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),"") ]

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

#### GlennUK

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

#### Snakehips

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

Hope that helps.

#### Nanaia

##### Board Regular
[ =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

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
Fabulous! The revised version worked! Thank you!

#### Snakehips

##### Well-known Member
Brilliant. You are welcome.

Replies
7
Views
166
Replies
25
Views
2K
Replies
10
Views
230
Replies
8
Views
206
Replies
10
Views
246