find the max of a range of numbers which are preceded with a letter
Results 1 to 3 of 3

Thread: find the max of a range of numbers which are preceded with a letter

  1. #1
    Board Regular
    Join Date
    Jun 2011
    Posts
    1,412
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default find the max of a range of numbers which are preceded with a letter

    I have a range of numbers which are preceded with a letter, e.g. A1392, A1587, A987, A3021, etc. Is there a way to find the maximum number of the numeric portion without using a help column?
    Last edited by yky; Aug 26th, 2019 at 02:00 AM.
    Excel 2010, Windows 10

  2. #2
    Board Regular James006's Avatar
    Join Date
    Apr 2009
    Posts
    3,288
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    1 Thread(s)

    Default Re: find the max of a range of numbers which are preceded with a letter

    Hi,

    You can test following Array formula

    Code:
    =MAX(SUBSTITUTE(A1:A4,"A","")*1)
    Hope this will help

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,305
    Post Thanks / Like
    Mentioned
    38 Post(s)
    Tagged
    3 Thread(s)

    Default Re: find the max of a range of numbers which are preceded with a letter

    Or this Array formula:

    Code:
    {=MAX(IFERROR(MID(A1:B10;2,LEN(A1:B10))*1,0))}
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

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
  •