Find highest number when mixedwith text in each cell

tonywatsonhelp

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

They look like this,

CA0939
CA0940
CA0935
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

<colgroup><col></colgroup><tbody>
</tbody>
 

Finalfight40

Board Regular
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

MrExcel MVP
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

Well-known Member
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
 

Some videos you may like

This Week's Hot Topics

Top