Share via

Find First Alphabetically

Anonymous
2019-03-21T15:55:58+00:00

I am looking for a formula or function that can help find the first item alphabetically in a text column.

I am doing some data profiling and have too many columns to just sort them alphabeticlly.

So, if my data is in a10:a999, what formula could I put in A1 to return the first item alphabetically in the range? Similarly, is there a version for last alphabetically?

Can it ignore blanks?

Will it work on a list of zip codes - where the characters are all numerpic, but the data is text?

If I grab a column, and use data/filter, Excel will display the values in the column in a pull down menu, and thos items are alphabetized. Can Iaccess whatever algorithm that is?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2019-03-22T20:32:42+00:00

    For the minimum value (excluding blanks), use the following array formula, confirmed with Ctrl+Shift+Enter:

    =LOOKUP(2,1/(IF(A10:A999<>"",COUNTIF(A10:A999,"<"&A10:A999))=0),A10:A999)

    For the maximum value (excluding blanks), use the following array formula, confirmed with Ctrl+Shift+Enter:

    =LOOKUP(2,1/(IF(A10:A999<>"",COUNTIF(A10:A999,">"&A10:A999))=0),A10:A999)

    This won't work for zip codes (probably).

    The algorithm used by Excel to populate dropdown lists is not available to us users.

    Was this answer helpful?

    4 people found this answer helpful.
    0 comments