Try this:

=IF(ISERR(SMALL(IF(ISTEXT($E$2:$E$41),ROW(INDIRECT ("1:"&ROWS($E$2:$E$41)))),ROWS($1:1))),"",INDEX($E $2:$E$41,SMALL(IF(ISTEXT($E$2:$E$41),ROW(INDIRECT( "1:"&ROWS($E$2:$E$41)))),ROWS($1:1))))

CTRL-SHIFT-ENTER (not just ENTER)
Copy down as far as need


"Michael" wrote:

Hello

I have a column which is 40 rows deep.
In the column E2:E41 which has text and "".
In a another column H I would like to just display the text on
sequential rows.

Example
E8 = Hotel
E15= Taxi
E23 = Restaurant
E40= Car

I would like in display in column H
H2= Hotel
H3= Taxi
H4= Restaurant
H4= Car

Using formula only no VBA

Thanks

Michael