Results 1 to 4 of 4

Thread: Find highest number when mixedwith text in each cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2014
    Posts
    2,326
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Find highest number when mixedwith text in each cell

    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

  2. #2
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find highest number when mixedwith text in each cell

    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.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,648
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Find highest number when mixedwith text in each cell

    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)

  4. #4
    Board Regular
    Join Date
    Feb 2014
    Posts
    2,326
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Find highest number when mixedwith text in each cell

    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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •