The simple, fast & easy-to-understand non-array method
that you posted works for both numbers and text in the source col A.

Should your source data in col A start from any row other than row1*, just a
slight adjustment is needed to col C's formula, viz replace ROW() with
ROWS($1:1). *Believe this might be what is tripping things up for you over
there

Eg assume col A's data starts in row3 down

You could use:
In B3: =IF(A3="","",ROW())
Ensure cells above, ie B1:B2 are left blank

In C3: =IF(ROWS($1:1)COUNT(B:B),"",INDEX(A:A,SMALL(B:B,R OWS($1:1))))
Copy B3:C3 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.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"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.