#VALUE! error help

[bluefeather8989]

I'm using this formula to Extract the last number in a cell. If there is no number then I get #VALUE! error. How can I fix this ??

=IF([@Item]="","",VALUE(RIGHT([@Item], 2)))

master list.xlsx
	B	C	D	E
2	X	#	Item	Isle
3	x	2	Shredded lettuce 2	A1
4	X	6	Cream of celery soup 6	A1
5	X	#VALUE!	Potatoes	A1
6	X	6	Tomato’s 6	A1
7	X	#VALUE!	Bread crumbs plain	A7
8	X	#VALUE!	Hawaii buns	A7
9	X	#VALUE!	Hamburger buns	A7
10	X	4	Coffee creamer hazelnut 4	A9
11	X	#VALUE!	Flower shells	A17
12		#VALUE!	Toilet paper charming ultra soft	A22
13	X	#VALUE!	Mountain Dew cube	A26
14	X	#VALUE!	Root beer	A26
15	X	3	Potato chips 3	J5
16	X	#VALUE!	Milk	A32
17	X	2	Butter 2	A32
18	X	#VALUE!	Cheese yellow	A33
19		2	Cubed pork 2	A34
20	C	5	Hamburger 1lb 5	A34
21	X	1	Roast 1	A34
22	X	12	Corn 12	A6
Poast

Cell Formulas
Range	Formula
C3:C22	C3	=IF([@Item]="","",VALUE(RIGHT([@Item], 2)))
D3	D3	=IF(Poast!H25="","",Poast!H25)
E3:E22	E3	=IF(D3="","",INDEX(Data!$C$3:$C$155,MATCH(TRUE,ISNUMBER(SEARCH(Data!$B$3:$B$155,D3)),0)))
D4	D4	=IF(Poast!H21="","",Poast!H21)
D5:D6	D5	=IF(Poast!H6="","",Poast!H6)
D7	D7	=IF(Poast!H22="","",Poast!H22)
D8:D9	D8	=IF(Poast!H17="","",Poast!H17)
D10,D12	D10	=IF(Poast!H24="","",Poast!H24)
D11	D11	=IF(Poast!H16="","",Poast!H16)
D13:D14	D13	=IF(Poast!H11="","",Poast!H11)
D15,D19	D15	=IF(Poast!H10="","",Poast!H10)
D16	D16	=IF(Poast!H3="","",Poast!H3)
D17	D17	=IF(Poast!H5="","",Poast!H5)
D18,D22	D18	=IF(Poast!H4="","",Poast!H4)
D20	D20	=IF(Poast!H13="","",Poast!H13)
D21	D21	=IF(Poast!H15="","",Poast!H15)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B3:E100	Expression	=$B3>0	text	NO

 

[Fluff]

How about
=iferror(VALUE(RIGHT([@Item], 2)),"")

 

[Phuoc]

Try this:

=IF([@Item]="","",IFERROR(VALUE(RIGHT([@Item], 2)),""))

 

[bluefeather8989]

How about
=iferror(VALUE(RIGHT([@Item], 2)),"")

thanks this works great

=IF([@Item]="","",IFERROR(VALUE(RIGHT([@Item], 2)),""))

 

[bluefeather8989]

Try this:

=IF([@Item]="","",IFERROR(VALUE(RIGHT([@Item], 2)),""))

sry didn't see your post but it does work thanks.

=IF([@Item]="","",IFERROR(VALUE(RIGHT([@Item], 2)),""))

 

[Fluff]

There is no need to check if Item is blank or not, as the Iferror handles that anyway.