Find the max form concaternated code

[Oubok]

I have a list of codes that were concaternated but do not follow in rows eg:
Row 10 DE 1 8 DE = area, 1 =counter, 8 = section
Row 11
Row 12 OC 1 4
Row 13 DE 2 8
Row 14 MY 1 23
Row 15
Row 16 NG 1 30
Row 17 DE 3 8
Row 18 OC 2 4
etc

I need to find out the highest (max) counter for each section (8,4,23 & 30) Can someone please help me??
Much appreciated,

 

[Dannyh1]

Hi,

Probably easiest to seperate numbers into different column

Could do this by either text to columns (use fixed width)

or use formula:

=VALUE(RIGHT(A1,LEN(A1)-FIND(" ",A1,4)))

 

[mikerickson]

You might want to de-concatenate those cells with TextToColumns.

But with your cocatenated data in A10:A18, this will return the Max counter of the section that is in C1.
Assuming the data looks like "DE 1 8".

=MAX((TRIM(RIGHT(A10:A18,LEN(C1)))=C1&"")*(0&TRIM(MID(SUBSTITUTE(A10:A18," ",REPT(" ",255)),255,255))))

This needs to be entered with Ctrl-Shift-Enter (Cmd+Return for Mac)

 

[Oubok]

Thanks im gonna try both ways to see if I can get the right answer. I should have mentioned that DE 1 8 is in coloum E.