Return a REAL blank cell in formula that will be ignored when sorted.

No matter what character you choose, it will sort like other characters. If it sorts to the bottom in an ascending sort, it will sort to the top in a descending sort. The only cells I see that sort to the bottom no matter what are blank cells and errors.

If you don't mind a bunch of error triangles, you could have the result of your formulas be an error instead of "". Any downstream formulas that reference those cells can use IFERROR instead of checking for "".

Or leave everything like it is but use a filter to filter out the rows with "" before or after sorting.

First, something you didn't ask about. The sort rank/value is affected by the Format of the Cell. If the Format is Automatic, the number 10 will have a higher sort value than the number 9. If the Format is Text, the number 10 will have a lower sort value than the number 9. If the Format is Number, the sort will be the same as Automatic. You probably knew all that .

Further, and more to your point, Numeric entries have a lower sort value than String entries. Null Strings have the lowest rank among String entries. And, Strings beginning with number characters sort between Null Strings.and String entries that begin with alpha characters.

Lastly, as you have found, Blank Cells sort to the bottom. I believe that is because it is what most users would like to have happen, to "compress" all the empty rows out of the range of interest, to the bottom, where rows not yet used normally live.

If I wanted a computed string value to go the the bottom, I would give it a value of "zzzzz", and then conditionally format the cells with zzzzz to have zero opacity text so that their content would be invisible.

Jerry

I'm with you that the null string should sort to the bottom like an actual blank cell, or it would at least be nice if that option was available. Unfortunately it does not. Neither does Excel.

There are no formulas that can create the equivalent of a blank cell. There were two text functions a few versions ago that could do it but that was because of a bug and that bug has been fixed.

I was hoping you could use a high-numbered non-visible unicode character (one of the various unicode space characters) in place of "" in your formula for your ascending sort but Numbers doesn't sort by ascii/unicode number so it doesn't help. They all get sorted to the top near the normal space character.

Other than filtering them out, I do not have a good solution.

You could use the  symbol (keys: alt 1 on this computer) instead of the null string “”.

As shown in the second table: conditionally format the cells with , to have zero opacity text so that the  symbol will be invisible.

Paul.

I don't know what your IF function looks like now but instead of something like

=IF(B2=0, "", B2*C2)

use

=IF(B2=0, 1/0, B2*C2)

or anything else that would generate an error.

Personally I don't like having error triangles in my spreadsheet. I presented it as an option but I think filtering is the better solution.

On this computer the function returns the  symbol.

Paul.

Sorry, I missed that part of your original question about needing to Sort Descending. Can't help you with that.

Jerry

Error triangles override everything else. I do not know of a way to hide them.