# Thread: Formula to find non-text item in a range (XL2003)

1. ## Formula to find non-text item in a range (XL2003)

I need a formula in A1 that will return the first non-text amount in a range (B1:B36),

B1 thru B36 is filled with formulae: =if(isblank(cell),"NO",cell). So B1:B36 will either have a number or the text "NO". If it has a number, it could be negative, zero or positive. I need the formula in A1 that will search down the B column and return the first number it finds (going top to bottom).

Lastly, if the entire range is filled with 36 "NO"s, the formula should return a zero.

2. ## Re: Formula to find non-text item in a range (XL2003)

If the formulas in B1:B36 refer to a contiguous range, say C1:C36, it would be easier to refer to that range than to B1:B36. The following array formula (confirm with Ctrl+Shift+Enter) will do what you want:

=INDEX(C1:C36,MIN(IF(ISNUMBER(C1:C36)*ROW(1:36)>0, ISNUMBER(C1:C36)*ROW(1:36))))

If you prefer to use B1:B36:

=IF(MIN(IF(ISNUMBER(B1:B36)*ROW(1:36)>0,ISNUMBER(B 1:B36)*ROW(1:36)))=0,0,INDEX(B1:B36,MIN(IF(ISNUMBE R(B1:B36)*ROW(1:36)>0,ISNUMBER(B1:B36)*ROW(1:36))) ))

If suppose someone will come up with a much simpler formula.

3. ## Re: Formula to find non-text item in a range (XL2003)

How about the array (confirm with ctrl-shift-enter):

=IF(COUNT(B1:B36),INDEX(B1:B36,MIN(IF(ISNUMBER(B1: B36),ROW(B1:B36)))),0)

Steve

4. ## Re: Formula to find non-text item in a range (XL2003)

That's a lot shorter! (I have a cold, and all I could come up with was complicated... <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

5. ## Thank you very much.

Thanks Steve and Hans for the replies. I used the array offered by Steve and it works fine.
<img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

6. ## Formula to pull first non-text entry from a column

Upon further testing, I've discovered that the formulas offered didn't quite work as I hoped. Probably something I did! <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>

Anyway, I've attached a sample worksheet with the problems highlighted. The formulas seem to be pulling the last numerical entry in the columns rather than the first. The specific formulas I need help with are in cells p39:u39 on sheet "001".

7. ## Re: Formula to pull first non-text entry from a column

Try this array formula in 001!P39:

=IF(COUNT(P2:P37),INDEX(P2:P37,MIN(IF(ISNUMBER(P2: P37),ROW(1:36)))),0)

The 1:36 is independent of the actual location of the cells.

8. ## Re: Formula to pull first non-text entry from a co

Hans, Thanks for the quick solution. It works fine.

I had gone back and tried the formula you originally gave me in <post#=554707>post 554707</post#> but I did change the 1:36 to 2:37. Just looked like the right thing to do.... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

9. ## Re: Formula to pull first non-text entry from a co

No, it's used as an index into the range of cells. The first cell of a range is always no 1, whether the range is B1:B36, P9:P1200 or W29039:W34195.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•