Return 1st Alphabetical Alphanumeric Text From Unsorted Range

    I have a range of cells B2:20 which has text in it. The range is sorted by cells A2:20, and I would like to do this without doing another sort if possible. The data looks like this in column B:


    CTR848
    AWT101
    LMT177
    AZG884
    BRE112
    BCW333
    CTQ553


    In one single cell (O1), I'd like to have the information:


    AWT101


    Is this possible to do without using VBA?


    Thanks in advance!

    Re: First Alphabetical Text In Range


    I'm probably over-complicating the problem, but this will work.
    [bfn]=INDEX($B$1:$B$20,MATCH(MIN(COUNTIF($B$1:$B$20,"<" & $B$1:$B$20)),COUNTIF($B$1:$B$20,"<" & $B$1:$B$20),0))[/bfn]
    Array formula confirmed with Control-Shift-Enter

    Re: First Alphabetical Text In Range


    Assuming there are no blanks in the range B2:B20 try


    =INDEX(B2:B20,MATCH(0,COUNTIF(B2:B20,"<"&B2:B20),0))


    confirmed with CTRL+SHIFT+ENTER


    ......or with blanks


    =INDEX(B2:B20,MATCH(0,IF(B2:B20<>"",COUNTIF(B2:B20,"<"&B2:B20)),0))


    also requires CTRL+SHIFT+ENTER

    Re: First Alphabetical Text In Range


    Quote from shg

    You'd like to have it in O1 based on what? Why not just type it in?


    Based on it being the first alphabetical entry. The list is updated and sorted multiple times per day and gets really long, but I just need to see the first entry at that point in time for record keeping.

    Re: First Alphabetical Text In Range



    That does it! Thanks a ton!

    Re: First Alphabetical Text In Range


    I knew I was over-complicating it. Why would I use Min when I know the min value is always 0? Not very bright.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login