I have a row of text (not sure if this is relevant, but the text isn't purely typed, it is a formula of concatenations and if statements).



Some cells appear blank (although they still have the same ifelse/concatenation formula, but end up being "")

So, basically I have a row of formulaic text that looks like this

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1

I want to use the range E1 through H1, and write a formula in a different worksheet that will order these by non-blank cells. So, I need to find the first nonblank cell, 2nd nonblank cell, 3rd, and 4th. Because some of these cells are blank, not all 4 of the spots will be fill, but the formula needs to make sure that if there is text in all of them then it will fill in order.


=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),1)),"") (for the first nonblank)

=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),2)),"") (for the 2nd nonblank), etc.

I have been trying this array formula, and it works for finding the first two non-blank text values, but when I try to find the 3rd and 4th nonblank values, if there is a blank value, instead of just a blank I am getting #NUM!.

Any ideas?

Just to be sure I understand, a blank cell is always a cell containing a null text string ? ("")

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Name A1:J1 as d and use the matrix formula:

=IFERROR(INDEX(d,SMALL(IF(d<>"",COLUMN(d)-CELL("col",d)+1),COLUMNS($E:E))),"")

for listing the first non-blank text. Copy the formula to the right to list second, third etc. non-blank text.

If you do not want to use IFERROR, you can instead use:

=IF(COLUMNS($E:E)>SUM(--(d<>"")),"",INDEX(d,SMALL(IF(d<>"",COLUMN(d)-CELL("col",d)+1),COLUMNS($E:E))))

Yep, that is Correct.

Thanks for the reply! However, none of these equations works for me. The first one allows me to enter the formula, but the cell is blank, whereas I would like a formula that when entered in four consecutive cells gives me the first four nonblank text cells within the range I specified.

The second column won't allow me to enter it all due to some sort of error :/

I decided that my original answer works well, except for one issue.

=IF(COUNTIF(E1:H1,"?*")>=2,INDEX(E1:H1,SMALL(IF(E1 :H1<>"",COLUMN(E1:H1)-COLUMN(E1)+1),1)),"") (for the first nonblank)

gives me what I want, but it needs to return a blank if there is no text to be returned (for example, if a particular row only has 3 non-blank text cells, it needs to still work for the 4th non-blank but just return "" so it appears blank). Instead, in the cases where there isn't any text to be returned, I get a #NUM! error.

Any tips for how to get rid of this error and replace it with empty text like ""?

Thanks!

My formulas work fine.

Attached Files

ListWithoutBlanks.xlsx (9.7 KB, 26 views)

Thank you!