Can't figure it out . . .

C

carl.m.kuklis

This may be simple but, I am stumped.

I have a range of six cells in a row on my spread sheet. Five cells are
empty and one contains data. I would like to find the single cell that
contains text and copy that text into a cell at the end of the row
 
B

Bill Kuunders

one way

concatenate the six into the seventh cell

=a1&a2&a3&a4&a5&a6

should do it
 
M

Max

[email protected] said:
I have a range of six cells in a row on my spread sheet. Five cells are
empty and one contains data. I would like to find the single cell that
contains text and copy that text into a cell at the end of the row
Click to expand...

One guess ..

Assume source cells in A1:F1

Put in G1's formula bar, then array-enter the formula by
pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER)
=INDEX(A1:F1,MATCH(TRUE,A1:F1<>"",0))

G1 will return the contents of the 1st cell from the left
that's not "empty", i.e. <>""

Copy G1 down if desired to return correspondingly
for other rows A2:F2, A3:F3, etc
 
D

Dave Peterson

You have several replies to your several posts.

This may be simple but, I am stumped.

I have a range of six cells in a row on my spread sheet. Five cells are
empty and one contains data. I would like to find the single cell that
contains text and copy that text into a cell at the end of the row
Click to expand...
 
M

Michael M

Hey Biff
Great formula. I'd been scratching my head over the OP's question.
But how does the( "z",255) make the formula work ???
Regards
Michael M
 
B

Biff

But how does the( "z",255) make the formula work ???
That formula is just the text version of this formula which is for numeric
values:

=LOOKUP(9.99999999999999E+307,A1:F1)

REPT("z",255) = zzzzzzzzz (255 z's)

So, the lookup value is zzzzzzzzz (255 z's)

Since there is a very high probabilty that the lookup_value will not be
found the formula returns the last TEXT value in the range.

I think Bob Phillips covers this here:

http://www.xldynamic.com/source/xld.LastValue.html

Biff
 
M

Michael M

Thanks
I'll bookmark the article.
Groan....I thought I was actually getting the hang of Excel, but lots more
to learn I'm afraid

Regards
Michael M
 
B

Biff

He also has a lot of info on Sumproduct which is worth a look.

Next to IF(), Sumproduct may be the most versatile function to have in ones
repertoire!

Biff
 
Top