CODEWITHSUNDEEP
excel
SGJones
SGJones

Reputation: 1

How to remove cells that contain formulas but no data (i.e. they appear to be blank, but are not)

I am looking for a formula to remove cells from one column without data in them to make the second column of data (much) shorter.

I have used =IFERROR(INDEX($BF$1:$BF$100,AGGREGATE(15,6,(ROW($BF$1:$BF$100)-ROW($BF$1)+1)/($BF$1:$BF$100<>""),ROWS(BH$1:BH2))),"") to remove cells WITHOUT DATA, however, the cells with no information but which contain formulas are still brought to the new column. How do I exclude these cells in the second column?

Upvotes: 0

Views: 188

Answers (1)

Erin Halbmaier
Erin Halbmaier

Reputation: 354

When I try your formula, it skips cells with formulas that calculate to "". Perhaps make sure your formulas are not evaluating to " ". Either that, or change to

=IFERROR(INDEX($BF$1:$BF$100,AGGREGATE(15,6,(ROW($BF$1:$BF$100)-ROW($BF$1)+1)/( ($BF$1:$BF$100<>"")*($BF$1:$BF$100<>" ")),ROWS(BH$1:BH2))),"")

to also exclude cells that have a single space.

Upvotes: 1

Related Questions