max value in column of alphanumerics, with blanks

Hi. See attached spreadsheet showing extract from a larger sheet.

I have a column of part numbers with alpha prefixes. The column also has blanks.

I need a formula that reports the maximum numeric suffix. i.e. 104801.

The top cell is my current effort, but this doesn't ignore the blanks so returns an error.

Note, the prefix will always be P-COL-.

Thanks.

=MAX(INDEX(SUBSTITUTE("P-COL-0"&A4:A500,"P-COL-","")*1,0))

Please Login or Register  to view this content.

Try this one

Please Login or Register  to view this content.

OK. 2 already there, so one more - array formula:

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

It is just adding in front of any cell contemts P-COL-0
so it cell is (empty) is treated as
P-COL-0
if cell is
P-COL-100101
then it becomes
P-COL-0P-COL-100101
but then with substitute
"P-COL-" by ""
they become "0" and "0100101" respectively, and finally multiplied by 1
0 and 100101

I used above "0" and 0 for purpose to emphasize that the first is text, and the next is number (only the latter is a good input for MAX function.
BTW.

Formula:

Please Login or Register  to view this content.

shall be enough