Removing text from a column

[cluettp]

Hello

I have a long column of text with some gaps; here's an example of what I mean:

	A
1	Monday
2
3	Friday
4	Saturday
5
6	Sunday
7
8	etc etc

<tbody>
</tbody>

If there a formula I can place in column B which just shows

	B
1	Monday
2	Friday
3	Saturday
4	Sunday
5
6
7
8

<tbody>
</tbody>

Thanks for reading.

 

[steve the fish]

Hi. Enter this into B1 using CTRL-SHIFT-ENTER

=IFERROR(INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)-ROW($A$1)+1),ROWS($A$1:A1))),"")

 

[JazzSP8]

In cell B1;

=IFERROR(INDEX($A:$A,SMALL(IF(ISTEXT($A:$A),ROW($A:$A),""),ROW(A2))),"")

It's an array formula so you'll need to press CTRL, SHIFT and ENTER to complete it properly, of done right you'll wrap it in { }

Drag down and it should do what you want

 

[cluettp]

In cell B1;

=IFERROR(INDEX($A:$A,SMALL(IF(ISTEXT($A:$A),ROW($A:$A),""),ROW(A2))),"")

It's an array formula so you'll need to press CTRL, SHIFT and ENTER to complete it properly, of done right you'll wrap it in { }

Drag down and it should do what you want

__________________

Thanks to all,

Steve the Fishes formula worked a treat; I found problems with JazzSP8's.

Someday I would like to understand how it works!