distilling a column of text entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brad - 09 Mar 2007 14:19 GMT

I am looking for a formula, as opposed to a VBA solution, where I have a
column of text entries, but with empty cells interspersed here and there
from top to bottom sort of like this for, say, cells A1:A6:

House
(empty cell)
Car
(empty cell)
(empty cell)
Dog

...like that with empty cells between each of the cells holding text.

Is there a way, via a formula to distill the above column so that it looks
like this:

House
Car
Dog

in another column or series of cells on the spreadsheet?

My thanks in advance if anyone can point me in the direction of a solution!
Brad

Reply to this Message

Bernard Liengme - 09 Mar 2007 14:37 GMT

Insert a row at the top and type a name for the column ("Data" is good)
Select this and all the data
Use Data | Filter | AutoFilter
Use the drop down handle in the top cell and select Non-blanks
Now do what you want to with them
best wishes

Signature

Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

>I am looking for a formula, as opposed to a VBA solution, where I have a
>column of text entries, but with empty cells interspersed here and there
[quoted text clipped - 20 lines]
> My thanks in advance if anyone can point me in the direction of a
> solution! Brad

Reply to this Message