Posted to microsoft.public.excel.worksheet.functions
|
|
Non-Array Play to Eliminate Blank Cells
http://www.cpearson.com/excel/noblanks.htm
--
Gary''s Student - gsnu200801
"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.
|