Max value of specific entry with alphanumeric prefix

[hope1025]

Hi everyone, I'm new here. Excel is fun it's like a puzzle to me.. However, this one problem I'm having a hard time figuring out.. Kindly help!
I need to get the max value of cell with a "9045-0151-ABC1-WXYZ-TO" prefix only. The row may contain blank cells also, please see below:

9045-0151-ABC1-WXYZ-TO-00063
9045-0151-DEF2-STYX-TI-00066
9045-0151-DEF2-STYX-TI-00079
9045-0151-DEF2-STYX-TI-00056
9045-0151-ABC1-WXYZ-TO-00018
9045-0151-DEF2-STYX-TI-00048
9045-0151-DEF2-STYX-TI-00049
9045-0151-DEF2-STYX-TI-00041
9045-0151-ABC1-WXYZ-TO-00043

Thank you...

 

[Andrew Poulsom]

Welcome to MrExcel.

Does this work for you?

Excel 2010
	A	B
1	9045-0151-ABC1-WXYZ-TO-00063	63
2	9045-0151-DEF2-STYX-TI-00066
3	9045-0151-DEF2-STYX-TI-00079
4	9045-0151-DEF2-STYX-TI-00056
5	9045-0151-ABC1-WXYZ-TO-00018
6	9045-0151-DEF2-STYX-TI-00048
7	9045-0151-DEF2-STYX-TI-00049
8	9045-0151-DEF2-STYX-TI-00041
9	9045-0151-ABC1-WXYZ-TO-00043
Sheet1

Cell Formulas
Range	Formula
B1	{=MAX(IF(LEFT(A1:A9,LEN("9045-0151-ABC1-WXYZ-TO"))="9045-0151-ABC1-WXYZ-TO",RIGHT(A1:A9,5)+0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[hope1025]

Really helpful Sir, but I need to get the whole alphanumeric value "9045-0151-ABC1-WXYZ-TO-00063" not just the last 2-digits.. Thank you..

 

[Andrew Poulsom]

That would be:

="9045-0151-ABC1-WXYZ-TO-"&TEXT(MAX(IF(LEFT(A1:A9,LEN("9045-0151-ABC1-WXYZ-TO"))="9045-0151-ABC1-WXYZ-TO",RIGHT(A1:A9,5)+0)),"00000")

 

[hope1025]

Wow that's amazing! many thanks Sir!!!