Highlight your data hit F5 Special Blanks OK Edit Delete Shift
Cells Up


"Peter" wrote:

Column A shows:

1.Apple
2.{Space}
3.Banana
4.{Space}
5.Yellow
6.Red

How do I make Column B show:

1.Apple
2.Banana
3.Yellow
4.Red

I've seen some hints at how to do this with numbers, but can you figure out
a solution with text?

Hint?:

A simple non-array play ..
In B1: =IF(A1="","",ROW())
In C1: =IF(ROW()COUNT(B:B),"",INDEX(A:A,SMALL(B:B,ROW()) ))
Copy B1:C1 down to cover the max expected extent of data in col A.
Minimize/hide away col B. Col C will return the results that you seek, all
neatly bunched at the top.