Return Text from a row of 20+ columns

[PGiessler]

Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30 has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

 

[Rick Rothstein]

Using your example range (which is no where near 30 columns wide), you can
use this array-entered formula AE1...

=IF(COUNTA(AA1:AD1)=0,"",INDEX(AA1:AD1,1,MATCH(1,--(AA1:AD1<>""),)))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

 

[MyVeryOwnSelf]

I have a table of about 30 columns. In each row, only one cell of the

30 has text in it. What I would like to do is add a column (say AE)
and have a formula that looks through column AA1:AD1, finds the cell
with text and returns it in AE1.

Maybe this will help:
=LOOKUP(2,1/(AA1:AD1<>""),AA1:AD1)

I'm not clear about rows vs. columns in the problem statement, so modify
"AA1:AD1" in the formula as needed.

 

[Bernard Liengme]

I like to work closer to home (cell A1) when testing.
So I used cells A1:K1. Please modify as needed
In A1:K1 I entered the values 1 thru 11; then I deleted B1 and changed E1
to the word "donkey"
So had: 1, <blank>, 3, 4, donkey, 6, 7, 8, 9, 10, 11

In L1 I entered the formula
=INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1)*COLUMN(A1:K1)))
and it returned the value "donkey"

The formula returns a #VALUE! error if there is no text in the range and a
totally wrong answer if there are two or more cells with text. This is a fix
for that
=IF(SUMPRODUCT(--ISTEXT(A1:K1)),INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1)*COLUMN(A1:K1))),
"None")


Either formula can cope with multiple blank cells. Cells which return
numeric values are also OK

best wishes

 

[RagDyer]

Assuming you made a typo and intended your range to be A1:AD1 (30 cells),
try this:

=INDEX(A1:AD1,MATCH("*",A1:AD1,0))

 

[RagDyer]

BTW -

The formula returns the *first* text entry -
*BUT*, you must remember that a null (zero length string, "" ) is considered
as TEXT by XL.
Therefore, if you have formulas in your range that you have configured to
return nulls in case of absent values, this formula wouldn't work unless you
replaced your null returns with perhaps zeroes.

 

[Francis]

Hi try

=HLOOKUP(REPT("Z",255),A1:AD1,1)

this will return the text in the range A1: AD1

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[PGiessler]

This works very well and seems to be a pretty straightforward approach. Thanks!

 

[PGiessler]

I had a little trouble with the second formula, but the first one did the
trick. Thanks for your help!

 

[PGiessler]

Thanks ... this works perfectly for what I need! Much appreciated!

 

[PGiessler]

Thanks for the formula ... it does exactly what I need it to do. I
appreceiate your help.

 

[RagDyer]

You're welcome, and thanks for the feed-back.