Find Max number value from text-number cell?

Hello world!
Please, i need to find Max number value(MAX invoice number INV-2025/16) from three General(text-number) columns:
[TABLE="class: grid, width: 400"]

INV-2016/16

INV-2018/16

INV-2017/16

INV-2021/16

INV-2020/16

INV-2019/16

INV-2025/16

INV-2022/16

INV-2023/16

INV-2024/16

[/TABLE]

These columns are on different sheets.
=INDIRECT("S"&MAX(IF(NOT(ISBLANK(S:S));ROW(S:S)))) no to this formula 'cause it's Volatile!
{=IF(ISBLANK($P$5:$P$109);ROW($P$5);SUMPRODUCT(MAX((ROW($P$5:$P$109))*($P$5:$P$109<>"")))+1)} this is promising
=RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))+2) this one for extracting numbers

INV-2023/16, INV-2024/16, INV-2025/16 which one is Max ??:cat:

Re: Find Max number value from text-number cell?

These three columns are on different sheets, range P9:P109. Problem might be, invoice numbers can be random entered like that INV-2025/16. This sixteen is year, so I want strip this to 2025. Thanks in advance. God bless!

Re: Find Max number value from text-number cell?

mAX is INV-2025/16. Invoice numbers are entered manually, so, i think probably last three "/16" is always the same. There may be error like INV2025/16, so last three for sure. tHANKS! Answer should be INV-2025/16. ="INV-" & yadayada & "/16"

Re: Find Max number value from text-number cell?

I thought so, as soon i saw name, excelxor
But, that amazing formula isn't working.
Here file: https://1drv.ms/x/s!ArVOOVnFRGX4g6V3VNrGyyh20-aw3w
Must go now to wedding!
Thanks so much!

Re: Find Max number value from text-number cell?

What to say? After 2 litres wine, and in the morning 4 litres water to cure hangover,.... amazing Excelxor! CHOOSE({1\2\3} , must be that I thought on that amazing N(1,(;;;.. formula! Again BIG THANKS!
My vba solution( not mine,Ozgrid):

Option Explicit
 Option Compare Text
 Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
   Const DefNumberFormat = """INV-""0""/yy"""
   Dim HighestNumber
   Application.EnableEvents = False
   'Which sheet?
   Select Case Sh.Name
     Case "Sheet1", "Sheet2", "Sheet3"
       'More then one cell?
       If Target.Count > 1 Then GoTo ExitPoint
       'Calculate the highest invoice number
       HighestNumber = Application.Max( _
         Sheets("Sheet1").Range("P:P"), _
         Sheets("Sheet2").Range("P:P"), _
         Sheets("Sheet3").Range("P:P"))
       'Which column?
       Select Case Target.Column
         Case Columns("Q").Column
           If IsEmpty(Target.Value) Then
             'Clear the invoice number
             Target.Offset(, 2).ClearContents
           Else
             With Target.Offset(, 2)
               'Only when column P is empty
               If Not IsEmpty(.Value) Then GoTo ExitPoint
               'Next number
               .Value = HighestNumber + 1
               .NumberFormat = Replace(DefNumberFormat, "yy", Format(Date, "yy"))
             End With
           End If
         Case Columns("P").Column
           With Target
             'Only if used
             If IsEmpty(.Value) Then GoTo ExitPoint
             .NumberFormat = Replace(DefNumberFormat, "yy", Format(Date, "yy"))
             'Must be the largest number!
             If .Value <> HighestNumber Then
               MsgBox "INVALID INVOICE NUMBER", vbExclamation, "Invalid Entry"
               Target.ClearContents
             End If
           End With
       End Select
   End Select
ExitPoint:
   Application.EnableEvents = True
 End Sub

I can't remember why code wasn't working, i think 'cause it's volatile. And in cell formula alongside: ="INV-"&IF(MAX(Table2[INVOICE No.];Table3[INVOICE No.];Table4[INVOICE No.])+1=1;" 0 ";MAX(Table2[INVOICE No.];Table3[INVOICE No.];Table4[INVOICE No.])+1)&"/16" This code and formula should provided me NEXT FREE No for invoice. Don't know what went wrong with all this code&formula solution:(

Re: Find Max number value from text-number cell?

But, what about, when I have INV-1/17 (January1st2017) that MID would not work!?

Re: Find Max number value from text-number cell?

Input will always be: INV- and /16(or 17,18...). So, now I need to strip all invoice numbers(array) from sheet1(column P), sheet2(col P), sheet3(col P), and then compare them to find max number. All that w/o Indirect or any other volatile funtion, or wrap it inside Index or Aggregate. Also, i need somehow error check when e.g. salesman wrong input invoice number: IN-2016 16. From start, there should be INV-(4 char.) and from back /16(3char.). That would be very complex formula: 1. check is first 4 char=INV- if not display message or #N/A 2.check is any of last 3char="/" if not display message or #N/A, when all that check is Ok then 3. from all trunc last 3char, from that result(INV-2016, INV-2017, INV-2018, INV-2019...) 4. left 4 char to extract numbers, and finally 5.Max(Large) that numbers. All that inside ="INV-"&AGGREGATE(Oh dear God, how wonderful would be to know Excel)&"/16"

Re: Find Max number value from text-number cell?

Quote from XOR LX;777889

Based on your new requirements, this post is now in serious need of an updated workbook with several pertinent examples together with expected results.

Regards

https://1drv.ms/x/s!ArVOOVnFRGX4g6V3VNrGyyh20-aw3w

Re: Find Max number value from text-number cell?

Quote from XOR LX;777964

So maybe something like:

="INV-"&AGGREGATE(14,6,MID(CHOOSE({1,2,3},Range1,Range2,Range3),5,FIND("/",CHOOSE({1,2,3},Range1,Range2,Range3))-5)+0,1)&"/16"

Adjust Range1, Range2 and Range3 as well as the final "/16" to suit.

Regards

Display More

=FIND("/",CHOOSE({1,2,3},Range1,Range2,Range3))-5

This part solo always returns 1(one), i don't understand. Anyway, formula is working BIG THANKS to all! I'm pleased with this tipe of solution. Please, check my other dilemas. ExcelXor thank you for your time and love for excel!

P.s.
Couldn't we use this:=RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))+2) to extract any number(e.g. INV-25/16 >> 2516), then somehow cut that to 25 and then ="INV-"&aggregate....... , just thinking, me dumb, it's impossible to have array in right(left), i guess.

Re: Find Max number value from text-number cell?

Quote from B.W.B.;778021

=FIND("/",CHOOSE({1,2,3},Range1,Range2,Range3))-5

This part solo always returns 1(one), i don't understand. Anyway, formula is working BIG THANKS to all! I'm pleased with this tipe of solution. Please, check my other dilemas. ExcelXor thank you for your time and love for excel!

P.s.
Couldn't we use this: =RIGHT(A1;LEN(A1)-MIN(FIND({0;1;2;3;4;5;6;7;8;9};A1&"0123456789"))+2) to extract any number(e.g. INV-25/16 >> 2516), then somehow cut that to 25 and then ="INV-"&aggregate....... , just thinking, me dumb, it's impossible to have array in Right(Left), i guess.

Sorry, FIND was missing ")", sorry ExcelXor! It's 3, it's working. Three char from "/" to right.

Re: Find Max number value from text-number cell?

Quote from XOR LX;778026

So we're all done here?

Regards

Yes, Thanks!