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:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Sunday[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]etc etc[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Monday[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Friday[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Saturday[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Sunday[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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!