Formula like MAX to find the highest value (contains text and numbers)

Hi all,
In column B I have numbers as such:
PUW-00001
PUW-00001
PUW-00002
PUW-00002
PUW-00003


I would like to have a formula at the top of the spreadsheet to tell me which number has been done last so I could enter it for my new documents without looking up and finding it manually. In this example it would be PUW-00003

If I do the simple MAX formula, it just displays 0 ( I guess due to text)

Is there a way around this?

Thank you!

Use this:

Formula:

Please Login or Register  to view this content.

Thank you!
Worked great with 1 small issue:
If value in B5 is 20
If I put B6 as 21 it will show 21..
If I put B4 as 21 it will not update... is there a way around it? I don't need it to make decisions where from, just need the highest number from the whole column

It find last number in the field as it was in your example.

But if they are not in ascending order then this will return you MAX value only last 5 digits must be numbers (as per example):

Formula:

Please Login or Register  to view this content.

Confirm formula with ctrl+shift+enter (not just enter)

Thank you - special K but it's an old document where I cant afford to add more columns on the side so just need 1 block to show me the last biggest number so I could type in the next number without looking up all the time.

zbor- some amazing help from you, really appreciate it... Sadly this formula doesn't work ( I edited it to =MAX(IFERROR(--RIGHT($B$2:$B$1000,5),0)) but it comes up with 0, with the previous one it properly showed the whole name

Did you confirm formula with ctrl+shift+enter?

Formula confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around your formula (you can't enter those brackets manually).

If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

I did, the braces appeared, but the result is 0 ;/

Do you have 5 characters at the end because if all data returns error then result is 0.

PUW-3 result: 0
PUW-03 result: 0
PUW-003 result: 0
PUW-0003 result: 0
PUW-00003 result: 3
PUW-000003 result: 3
PUW-0000003 result: 3

all are in the same format of -
PUW-00006
PUW-00007
PUW-00008
PUW-00009
PUW-00010
PUW-00011
PUW-00012
PUW-00013
PUW-00014

Space at the end?
Attach a sample workbook.

Delete all other relevant data.

file attached

Than can do the trick too.

But formula above works in this example:

Formula:

Please Login or Register  to view this content.

Returns 5

=INDEX(B3:B100,MATCH("*"&(TEXT(MAX(IFERROR(--RIGHT(B3:B$100,5),0)),"00000")),B$3:B$100,0)) this worked when I changed B100 to B10000 :>

Great!! Thank you all! this forum is legendary!

ah damn, I've seen that I have another worksheet which has more numbers.....

how to make this formula
=INDEX(B3:B100,MATCH("*"&(TEXT(MAX(IFERROR(--RIGHT(B3:B$100,5),0)),"00000")),B$3:B$100,0))

Check the main worksheet and the second worksheet as well? ;/ (becoming slight rocket science :D )

The formulas above work.


sadly after getting the formulas to work I found out that the spreadsheet has a few work sheets which have more numbers so I need the foruma to compare a few worksheets aswell

yes please

Thank you guys!

What I've did was:
=MAX(IFERROR(--SUBSTITUTE(B3:B1000,LEFT(B3:B1000,4),""),0)) on the side of each work sheet which told me the max number without the puw-
then where I need the data I just added a MAX formula that refered to the cell in each worksheet with the max value and displayed the highest number nice and easy

Thank you!