Stacking MAX and RIGHT and LEN functions

[Yamezz]

I have data that contains order numbers preceded by "#", such as #1985, #1986, #1987 etc. I want to find the maximum and minimum order values in a selection, but simply using =MAX(C2:C32) returns "0".
I can use =RIGHT(C2,LEN(C2)-1) to reduce #1985 to simply 1985, but I don't know how to find the maximum without using a helper column. Is there a way?

 

[Fluff]

How about

=MAX(IFERROR(--MID(C2:C100,2,100),0))

This may need to be confirmed with Ctrl Shift Enter.

 

[Yamezz]

Fluff, both normal Enter and Ctrl + Shift + Enter result in "0"

 

[Fluff]

In that case can you post some sample data using the XL2BB add-in, as it works for me

Fluff.xlsm
	C	D
1
2	#1985	1998
3	#1986
4	#1987
5	#1998
6	#1989
7	#1990
8	#1991
9
Data

Cell Formulas
Range	Formula
D2	D2	=MAX(IFERROR(--MID(C2:C100,2,100),0))

 

[MrRajKumar]

Another option with just enter,

=AGGREGATE(14,6,--MID(C2:C100,2,10),1)

 

[Yamezz]

Sorry Fluff, I made a referencing error. Your approach works, as does @MrRajKumar.
Thanks.

 

[Fluff]

Glad we could help & thanks for the feedback.