Find highest number when mixedwith text in each cell

[tonywatsonhelp]

Hi everyone,
I need to find the highest invoice number but the cels are not numbers but text

They look like this,

[TABLE="width: 86"]
<colgroup><col></colgroup><tbody>[TR]
[TD]CA0939[/TD]
[/TR]
[TR]
[TD]CA0940[/TD]
[/TR]
[TR]
[TD]CA0935[/TD]
[/TR]
[TR]
[TD]CA0928

so all I want to do is return the highest number into Sheet Control AB25

so it would be CA0940
its in sheet "RAW" Column A
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Finalfight40]

Would this work?

=VLOOKUP("*"&MAX(RIGHT(A2:A5, 4)+0), A2:A5, 1, FALSE)

Would need to adjust the range.

Entered as an array with ctrl + shift+ enter.

 

[mikerickson]

The CSE formula (entered with Ctrl-Shift-Enter) will return the highest index of CA numbers in column A. The "CA" is not part of the returned value.

=MATCH(0, COUNTIF(A:A,">CA"&TEXT(ROW(A1:A9999),"0000")),0)

 

[tonywatsonhelp]

Thank you final flight but thats what I tried and get CA9 above CA4000 (as examples) because it see them as alphabetical.
mikeerickson, brilliant, thank you fixed that problem perfectly
Tony