Forum Discussion

Brass Contributor

Oct 16, 2022

Solved

How to ignore blank cells when using the SORTBY formula

I have a SORTBY formula that spills a list from Column A into Cell C3. The list is sorted by the words' length, from smallest to largest. However there are blank cells in the list that I wish the for...

excel

HansVogelaar
Oct 16, 2022
Asparagus

Yes:

=LET(Filtered, FILTER(A3:A12,A3:A12<>"",""), SORTBY(Filtered,LEN(Filtered)))

HansVogelaar

MVP

Oct 16, 2022

Asparagus

Yes:

=LET(Filtered, FILTER(A3:A12,A3:A12<>"",""), SORTBY(Filtered,LEN(Filtered)))

bassammahfouz

Copper Contributor

May 15, 2023

Thanks @HansVogelaar, the formula worked but, it is not sorting in alphabetical order?

Lorenzo
Silver Contributor
May 16, 2023
bassammahfouz
If you read carefully, the initial request was to sort by the Length of each value, what @HansVogelaar formula does
If you want to sort your Filtered data A-Z, adjust as:
=LET(Filtered, FILTER(A3:A12,A3:A12<>"",""), SORT(Filtered))

or simply: SORT(FILTER(A3:A12,A3:A12<>"",""))
- bassammahfouz
  Copper Contributor
  May 17, 2023
  Thank you