On Thu, 26 Feb 2009 12:07:02 -0800, 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 can’t
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.

Here's one way.

This formula must be **array-entered** in some row. Then fill down until you
start getting blanks.

"rng" refers to the range of interest. If you are using a version of Excel
prior to Excel 2007, rng cannot be an entire column (but could be
$A$1:$A$65535)

=IF(ISERR(ADDRESS(LARGE(ISTEXT(rng)*ROW(rng),
COUNTA(rng)+1-ROWS($1:1)),1,4)),"",ADDRESS(LARGE(
ISTEXT(rng)*ROW(rng),COUNTA(rng)+1-ROWS($1:1)),1,4))


To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl<shift while hitting <enter. If you did this
correctly, Excel will place braces {...} around the formula.

Then Fill Down until you start getting blanks.
--ron