I have a row of 24 cells, some are blank and others have text. I am trying
to create a formula that returns the first instance of text being used. I
have tried using a match, but it gives me an error because I am trying to
pull text not a number.

thanks

Here's one dart throw ..
Assume your 24 source cells are A2:A25
Place in say, B2, normal ENTER to confirm:
=INDEX(A2:A25,MATCH(TRUE,INDEX(ISTEXT(A2:A25),),0) )
Bullseye? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Chad Wodskow" wrote:
I have a row of 24 cells, some are blank and others have text. I am trying
to create a formula that returns the first instance of text being used. I
have tried using a match, but it gives me an error because I am trying to
pull text not a number.

thanks

=INDEX(A1:Z1,MATCH(TRUE,A1:Z1<"",0))

This is an array formula that must be entered with CNTRL-SHFT-ENTER rather
than just the enter key.
--
Gary''s Student - gsnu200908


"Chad Wodskow" wrote:

I have a row of 24 cells, some are blank and others have text. I am trying
to create a formula that returns the first instance of text being used. I
have tried using a match, but it gives me an error because I am trying to
pull text not a number.

thanks

The earlier presumes you want to extract the 1st occurence of TEXT in A2 down

If you just want to extract the contents of the 1st non empty cell,
irrespective whether its a real number or text number or text string
use this in B2, normal ENTER to confirm:
=INDEX(A2:A25,MATCH(TRUE,INDEX(A2:A25<"",),0))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

Max,

That worked perfectly, now how do I return the 2nd instance?

Chad

"Chad Wodskow" wrote:

I have a row of 24 cells, some are blank and others have text. I am trying
to create a formula that returns the first instance of text being used. I
have tried using a match, but it gives me an error because I am trying to
pull text not a number.

thanks

This will return the first TEXT entry in the range (excludes formula
blanks):

=INDEX(A2:A25,MATCH("?*",A2:A25,0))

how do I return the 2nd instance?

Try this...

Assume you want the results starting in cell C2.

Array entered** in C2 and copied down as needed:

=IF(ROWS(C$2:C2)COUNTIF(A$2:A$25,"<"),"",INDEX(A :A,SMALL(IF(A$2:A$25<"",ROW(A$2:A$25)),ROWS(C$2:C 2))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

C2 will return the 1st non-empty cell
C3 will return the 2nd non-empty cell
C4 will return the 3rd non-empty cell
etc
etc

--
Biff
Microsoft Excel MVP


"Chad Wodskow" wrote in message
...
Max,

That worked perfectly, now how do I return the 2nd instance?

Chad

"Chad Wodskow" wrote:

I have a row of 24 cells, some are blank and others have text. I am
trying
to create a formula that returns the first instance of text being used.
I
have tried using a match, but it gives me an error because I am trying to
pull text not a number.

thanks

.. how do I return the 2nd instance?
Put this in B2, array-enter, ie press CTRL+SHIFT+ENTER to confirm:
=INDEX(A:A,SMALL(IF(ISTEXT(A$2:A$25),ROW(A$2:A$25) ),ROWS($1:1)))
Copy down as far as required. B2 returns the 1st TEXT data in A2 down, B3
returns the 2nd TEXT data (2nd instance), and so on. #NUM! will signal the
exhaustion of all TEXT data
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---