Looking for Max of a range of numbers in strings

[twl2009]

I am trying to get the highest number from a range of order no. that all start with 2 letters,Eg 'EC1189' etc.
I can strip the number from a cell easily with


Value(right(H6,4))


And can get the maximum from a small range with

max(Value(right(H6,4)),Value(right(H7,4)),Value(right(H8,4)))


But I cant figure out how to get maximum from a range, I have tried

max(Value(right(H6:H20,4)))
and
max(Value(right(H6:H,4)))
and
max(Value(right(H6,4)):Value(right(H20,4)))

and a few other variations, but nothing is working.

Any help very gratefully received

Twl

 

[JoeMo]

Note the formula in I6 is an array formula. Confirmed with ctrl+shift+enter. Assumes the numeric part of the strings are always 4 digits.

Excel Workbook
	H	I
6	EC1202	1209
7	EC1198
8	EC1190
9	EC1200
10	EC1209
11	EC1197
12	EC1199
13	EC1201
14	EC1194
15	EC1193
16	EC1195
17	EC1191
18	EC1196
19	EC1192
20	EC1189
Enter Data Here

 

[twl2009]

Thanks Joe, thats has sorted it. Is it possible to add an if component so that it only includes orders that start with 'EC' and ignores all others?

twl

 

[JoeMo]

Thanks Joe, thats has sorted it. Is it possible to add an if component so that it only includes orders that start with 'EC' and ignores all others?

twl

You are welcome.

For your new question, try this (also an array formula so confirm with shift + ctrl + enter):

=MAX(IF(LEFT(C2:C20,2)="EC",RIGHT(C2:C20,4)+0))

Change range to match your data.

 

[Peter_SSs]

If you are interested in formulas that do not require the Ctrl+Shift+Enter confirmation, you could try these (requires Excel 2010 or later)
I6 obtains the max for all cells
I7 the max for those starting with EC

Excel Workbook
	H	I
6	EC1202	1209
7	EC1198	1202
8	EC1190
9	BC1200
10	BC1209
11	BC1197
12	BC1199
13	BC1201
14	BC1194
15	BC1193
16	BC1195
17	BC1191
18	BC1196
19	BC1192
20	BC1189
Max