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:


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


    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


    confirmed with CTRL+SHIFT+ENTER

    ......or with blanks


    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!