If you don't want to clutter up your file with helper cells try this array
formula** :

Assuming {Space} means it's an EMPTY cell.

=IF(ROWS(B$1:B1)<=COUNTA(A$1:A$6),INDEX(A$1:A$6,SM ALL(IF(A$1:A$6<"",ROW(A$1:A$6)),ROWS(B$1:B1))-ROW(A$1)+1),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Peter" wrote in message
...
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.