Finding Text Value in Column

[Guest]

Thanks for any help anyone can give me.

My problem is this:

I have several columns in a range (B:F). Within each row in that range, there is one and only one cell that contains a text string ("words"). All the other cells in that row are blank ("").

E.g.

A B C D E F
1 tree
2 su
3 eart
4 tree
5 sky
6 sun
7 tre

I would like to put a formula in Column A that will return the text string in the particular row, so that my A column will look like:

tree
su
eart
tre
sk
su
tre

I know that I can use embedded If statements for seven Ifs, but the number of columns is actually more than seven.

I would prefer a formula, but if the only way to do it is with code, I would be happy nonetheless.

Thanks again.

 

[Peo Sjoblom]

One way

=INDEX($A2:$F2,,MATCH(TRUE,LEN($A2:$F2)>0,0))

entered with ctrl + shift & enter

copy down 7 rows

--

Regards,

Peo Sjoblom

Thanks for any help anyone can give me.

My problem is this:

I have several columns in a range (B:F). Within each row in that range,

there is one and only one cell that contains a text string ("words"). All
the other cells in that row are blank ("").

E.g.

A B C D E F
1 tree
2 sun
3 earth
4 tree
5 sky
6 sun
7 tree

I would like to put a formula in Column A that will return the text string

in the particular row, so that my A column will look like:

tree
sun
earth
tree
sky
sun
tree

I know that I can use embedded If statements for seven Ifs, but the number

of columns is actually more than seven.

 

[Alan]

Try
=IF(COUNTA(B2:F2)>0,INDEX(B$2:F$2,MATCH("*",B$2:F$2,0)),"")
The COUNTA is to avoid an error message if there is no value at all,
Alan.

Thanks for any help anyone can give me.

My problem is this:

I have several columns in a range (B:F). Within each row in that range,

there is one and only one cell that contains a text string ("words"). All
the other cells in that row are blank ("").

E.g.

A B C D E F
1 tree
2 sun
3 earth
4 tree
5 sky
6 sun
7 tree

I would like to put a formula in Column A that will return the text string

in the particular row, so that my A column will look like:

tree
sun
earth
tree
sky
sun
tree

I know that I can use embedded If statements for seven Ifs, but the number

of columns is actually more than seven.

 

[Alan]

Without the dollar signs, sorry,
=IF(COUNTA(B2:F2)>0,INDEX(B2:F2,MATCH("*",B2:F2,0)),"")

 

[Ron Gatewood]

Hi:

In cell A1 put this formula

=B1&C1&D1&E1&F1

Copy it down to A7

Thanks for any help anyone can give me.

My problem is this:

I have several columns in a range (B:F). Within each row in that range,

there is one and only one cell that contains a text string ("words"). All
the other cells in that row are blank ("").

E.g.

A B C D E F
1 tree
2 sun
3 earth
4 tree
5 sky
6 sun
7 tree

I would like to put a formula in Column A that will return the text string

in the particular row, so that my A column will look like:

tree
sun
earth
tree
sky
sun
tree

I know that I can use embedded If statements for seven Ifs, but the number

of columns is actually more than seven.

 

[Aladin Akyurek]

=LOOKUP(REPT("z",255),B2:F2)

Thanks for any help anyone can give me.

My problem is this:

I have several columns in a range (B:F). Within each row in that range,

there is one and only one cell that contains a text string ("words"). All
the other cells in that row are blank ("").

E.g.

A B C D E F
1 tree
2 sun
3 earth
4 tree
5 sky
6 sun
7 tree

I would like to put a formula in Column A that will return the text string

in the particular row, so that my A column will look like:

tree
sun
earth
tree
sky
sun
tree

I know that I can use embedded If statements for seven Ifs, but the number

of columns is actually more than seven.