Finding largest number with custom made ID numbers

I'm looking for a way to find the largest ID number in my ID column, but the normal MAX, doesn't seem to work.

The IDs are
REQ-001
REQ-002
..and so on

I just want a formula to return the highest/largest REQ-XXX in a cell at the top of the worksheet.

Any suggestions?

Thanks

Originally Posted by NBVC

If they all begin with REQ and end in 3 digits, try:

="REQ-"&TEXT(MAX(RIGHT(A1:A100,3)+0),"000")

where A1:A100 is the range and confirm the formula with CTRL+SHIFT+ENTER not just ENTER.

Thanks for your help - works great.