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

• Re: First Alphabetical Text In Range

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

[SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

• 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

Hi goatdan

Sorry, too late.

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