ExcelBanter - Can't figure it out . . .

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Can't figure it out . . . (https://www.excelbanter.com/excel-worksheet-functions/86459-cant-figure-out.html)

Can't figure it out . . .

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

Can't figure it out . . .

one way

concatenate the six into the seventh cell

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

should do it

--
Greetings from New Zealand
Bill K
"
om wrote in message
...
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

Can't figure it out . . .

" wrote:
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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Can't figure it out . . .

You missed the new users group! Still time!

=LOOKUP(REPT("z",255),A1:F1)

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

Biff

"
om wrote in message
...
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

Can't figure it out . . .

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

"Biff" wrote:

You missed the new users group! Still time!

=LOOKUP(REPT("z",255),A1:F1)

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

Biff

"
om wrote in message
...
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

Can't figure it out . . .

But how does the( "z",255) make the formula work ???
=LOOKUP(REPT("z",255),A1:F1)

That formula is just the text version of this formula which is for numeric
values:

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

=LOOKUP(REPT("z",255),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 he

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

Biff

"Michael M" wrote in message
...
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

"Biff" wrote:

You missed the new users group! Still time!

=LOOKUP(REPT("z",255),A1:F1)

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

Biff

"
om wrote in message
...
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

Can't figure it out . . .

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

"Biff" wrote:

But how does the( "z",255) make the formula work ???
=LOOKUP(REPT("z",255),A1:F1)

That formula is just the text version of this formula which is for numeric
values:

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

=LOOKUP(REPT("z",255),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 he

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

Biff

"Michael M" wrote in message
...
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

"Biff" wrote:

You missed the new users group! Still time!

=LOOKUP(REPT("z",255),A1:F1)

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

Biff

"
om wrote in message
...
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

Can't figure it out . . .

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

"Michael M" wrote in message
...
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

"Biff" wrote:

But how does the( "z",255) make the formula work ???
=LOOKUP(REPT("z",255),A1:F1)

That formula is just the text version of this formula which is for
numeric
values:

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

=LOOKUP(REPT("z",255),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 he

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

Biff

"Michael M" wrote in message
...
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

"Biff" wrote:

You missed the new users group! Still time!

=LOOKUP(REPT("z",255),A1:F1)

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

Biff

"
om wrote in message
...
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