testing cells with text in array formula

[jb]

I have an excel sheet test_blank which contains list from b5 to b15 with empty cells in between. These empty cells have "" values.

I have an array formula in c5 to c15 which is written to generate a list without blank cells.
Means c5 has value aa, c6 has bb, c7 must have cc, c8 must have dd and so on.

My formula is not working in c5 to c15. I have tried to apply istext with len function. But it does not work.

I checked formula with list of values where empty cells does not have "". It works. I have shown data in e5 to e15 and formula in f5 to f15.

I have attached sample sheet. My original sheet will contain a list of values where empty cell will contain either text or "".

 

[bosco_yip]

1] Range B5:B15 contain non-breaking spaces, maybe copied from the external source (HTML).

2] Try in C5, copied down :

=IFERROR(INDEX($B$5:$B$15,AGGREGATE(15,6,ROW($B$1:$B$11)/(CODE($B$5:$B$15)>96),ROW(B1))),"")

Regards
Bosco

 

[jb]

Sir it is not working.
I have used following array formula. It works perfectly if the cell does not contain anything.

=IFERROR(INDEX($B$5:$B$15,SMALL(IF(ISTEXT($B$5:$B$15),ROW($B$1:$B$11),""),ROW(B1))),"")

I also know the problem. ISTEXT($B$5:$B$15) is not returning false for the cells having "" as a value.

I just want a solution so that istext return false for cells having value ""[/quote]

 

[Peter Bartholomew]

One problem I would note with the proposed test
AND(ISTEXT($B$5:$B$15),LEN($B$5:$B$15>0))
is that the AND operation aggregates all 11 rows into a single result so that you will have no output if any row is fails to meet the conditions specified.
In more detail
LEN($B$5:$B$15>0)
can only evaluate as 4 or 5 depending on whether the inequality is true or false. Both 4 and 5 are treated as TRUE when used as parameters of the AND() function.

ISTEXT($B$5:$B$15) * (LEN($B$5:$B$15)>0)

would get your formula working.

For interest, I have attached a workbook that implements your method as a single multicell array formula rather than a list of single-cell array formulae. I have also introduced an index column 'k' on the grounds that it less distracting than bringing ROW() function into the formula with references to cells that are not part of the solution space.