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"]

    [tr]


    [td]

    INV-2016/16

    [/td]


    [td]

    INV-2018/16

    [/td]


    [td]

    INV-2017/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2021/16

    [/td]


    [td]

    INV-2020/16

    [/td]


    [td]

    INV-2019/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2025/16

    [/td]


    [td]

    INV-2022/16

    [/td]


    [td]

    INV-2023/16

    [/td]


    [/tr]


    [tr]


    [td]

    INV-2024/16

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/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?


    So which is the maximum for the dataset you give in post #1?


    Are the last three characters always /16?
    Are the first four characters always INV-?

    So the answer for your dataset would be 2025? Or is that INV-2025/16?


    Regards

  • 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?


    Assuming the three sheets in question are called "Sheet1", "Sheet2" and "Sheet3" (change as required):


    ="INV-"&AGGREGATE(14,6,MID(CHOOSE({1,2,3},Sheet1!P9:P109,Sheet2!P9:P109,Sheet3!P9:P109),5,4)+0,1)&"/16"


    Regards


    Advanced Excel Techniques: http://excelxor.com/

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


    It doesn't work because for some reason you've changed this part:


    CHOOSE({1,2,3}


    to:

    CHOOSE({1;2;3}


    which is a completely different thing!


    Perhaps you're not using an English language version of Excel, i.e. one in which the argument separator in formulas is the semicolon, not the comma? If so, this does not mean that commas in array constants should also be changed to semicolons; that would depend on the language version of Excel you are using.


    For example, most mainland European versions of Excel would use:


    CHOOSE({1\2\3}


    here, though I'm not sure if that applies to you.


    Regards


    P.S. Enjoy the wedding!

  • 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):


    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?


    Quote from B.W.B.;777674

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


    Naturally, because...


    Quote from XOR LX;777039

    Are the last three characters always /16?



    Quote from B.W.B.;777046

    i think probably last three "/16" is always the same.

  • 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?


    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

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


    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

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



    =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.


    Can you explain how you arrive at that evaluation? Perhaps with an example?


    Quote from B.W.B.;778021

    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.


    What are you saying here? You want me to add something such that the end part, e.g. "/16", "/17", etc., is automatically detected so that you don't have to manually amend it in the formula?


    Regards

  • 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.