Hi,

Put his formula in any cell (except row 1). Drag down and it will return the
row numbers of columnA that have text. It will start producing #NUM errors
when it stops finding text in the range

=SMALL(IF(ISTEXT($A$1:$A$12),ROW($A$1:$A$12)),ROW( )-ROW($D$1))


'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correctly then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array

Mike


"M" wrote:

I have a column that contains empty cells and text. I want to return the row
reference of the cells that contain text. For example€¦

A2 = empty cell
A3 = empty cell
A4 = 2L
A5 = empty cell
A6 = 2W
A7 = empty cell
A8 = 2L2W
A9 = empty cell
A10 = empty cell
A11 = 2L2W
A12 = empty cell

The formula would return 4. Thanks to Luke I have€¦

{=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))}

However I need to copy this formula down a column. The formula should return
4 then 6 then 8 then 11 etc. The blank cells are randomly placed. I cant
sort the data as I have other equations in the sheet.

To add to this problem I need to repeat the row number if the original cell
contains 4 or more characters. So the formula should return 4 then 6 then 8
then 8 then 11 then 11 etc.

Thanks for your help.