_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Try this...

Assumes no cells contain formulas that return formula blanks (which are TEXT
strings):

=LOOKUP("zzzzzz",A1:A4)

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

use a wildcard for the match and then index to return the value

=INDEX(A1:A4,MATCH("*",A1:A4,0))
--
If this helps, please remember to click yes.


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Art,

Try this

=LOOKUP(2,1/(ISTEXT(A1:A4)),A1:A4)

Mike

"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

even simpler, I misread your post and believed there were numbers in the
other cells

=LOOKUP(2,1/(A1:A4<""),A1:A4)

Mike

"Mike H" wrote:

Art,

Try this

=LOOKUP(2,1/(ISTEXT(A1:A4)),A1:A4)

Mike

"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

I forgot to mention that cells A1:A4 do contain a formula. All evaluate to an
empty string except for cell A3. I don't think any of the solutions resolve
this issue.

Sorry,
Art


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

I think mine does.

--
Rick (MVP - Excel)


"Art" wrote in message
...
I forgot to mention that cells A1:A4 do contain a formula. All evaluate to
an
empty string except for cell A3. I don't think any of the solutions
resolve
this issue.

Sorry,
Art


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Hi,

The solution I posted solves the issue provided you are truly returning null
string and not a space.

Paul C solution works even with a space

Nuje

"Art" wrote:

I forgot to mention that cells A1:A4 do contain a formula. All evaluate to an
empty string except for cell A3. I don't think any of the solutions resolve
this issue.

Sorry,
Art


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Yes, but the number of rows in the column will vary. That's another problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

.

Mike, I see that now. I had tested it but made an error. Thanks.
--
Art


"Mike H" wrote:

Hi,

The solution I posted solves the issue provided you are truly returning null
string and not a space.

Paul C solution works even with a space

Nuje

"Art" wrote:

I forgot to mention that cells A1:A4 do contain a formula. All evaluate to an
empty string except for cell A3. I don't think any of the solutions resolve
this issue.

Sorry,
Art


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

Art,

Don't start another thread, you have answers in this one that work unless of
course you can tell us why they didn't work.

Mike

"Art" wrote:

Yes, but the number of rows in the column will vary. That's another problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

.

the number of rows in the column will vary.
I forgot to mention that cells A1:A4 do contain
a formula. All evaluate to an empty string
except for cell A3

Try this...

=INDEX(A1:A10,MATCH("?*",A1:A10,0))

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
I forgot to mention that cells A1:A4 do contain a formula. All evaluate to
an
empty string except for cell A3. I don't think any of the solutions
resolve
this issue.

Sorry,
Art


"Art" wrote:

_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into A5
that will return this text entry.

Thanks,
Art

For future consideration: You should **not** simplify your questions for
us... the answers you get for questions you ask tend to be tailored to the
conditions you say you have... if you don't tell us what those conditions
actually are, you will usually get answers that will need to be modified
when you actually do tell us what the conditions are, so you might as well
tell them to us at the beginning and save those who you are asking to help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into
A5
that will return this text entry.

Thanks,
Art

.

Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty as
shown. These are a series of datasets. The information in Col B is used to
define the position and size of each dataset. For instance, B1, which is 4,
means that a dataset begins in this row and is 4 rows tall. B2, which is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one non-empty
string. In the example, the second dataset (A5:A6) is all empty strings.

I need a formula for Column C that on the first row of each dataset returns
the non-empty string in the dataset. This formula will return an empty string
if there are no non-empty strings in the dataset or if this row is not the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions for
us... the answers you get for questions you ask tend to be tailored to the
conditions you say you have... if you don't tell us what those conditions
actually are, you will usually get answers that will need to be modified
when you actually do tell us what the conditions are, so you might as well
tell them to us at the beginning and save those who you are asking to help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula into
A5
that will return this text entry.

Thanks,
Art

.


.

This works but seems to be overly complicated. It's the only thing I could
get to work based on every possible combination of data I tried. Shorter
formulas worked on your data as posted but when I changed the data they
crashed.

What version of Excel are you using?

This will work in all versions:

=IF(B10,LOOKUP("zzzzzz",CHOOSE({1,2},"",INDEX(A1: A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0) ))),"")

This will only work in Excel 2007:

=IF(B10,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1: A$9,1):INDEX(A1:A$9,B1),0)),""),"")

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty as
shown. These are a series of datasets. The information in Col B is used to
define the position and size of each dataset. For instance, B1, which is
4,
means that a dataset begins in this row and is 4 rows tall. B2, which is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one non-empty
string. In the example, the second dataset (A5:A6) is all empty strings.

I need a formula for Column C that on the first row of each dataset
returns
the non-empty string in the dataset. This formula will return an empty
string
if there are no non-empty strings in the dataset or if this row is not the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions for
us... the answers you get for questions you ask tend to be tailored to
the
conditions you say you have... if you don't tell us what those conditions
actually are, you will usually get answers that will need to be modified
when you actually do tell us what the conditions are, so you might as
well
tell them to us at the beginning and save those who you are asking to
help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula
into
A5
that will return this text entry.

Thanks,
Art

.


.

Biff,

Thanks for taking this on. I am headed for Christmas shopping now but will
give it a try later on. I am using Excel 2007.
--
Art


"T. Valko" wrote:

This works but seems to be overly complicated. It's the only thing I could
get to work based on every possible combination of data I tried. Shorter
formulas worked on your data as posted but when I changed the data they
crashed.

What version of Excel are you using?

This will work in all versions:

=IF(B10,LOOKUP("zzzzzz",CHOOSE({1,2},"",INDEX(A1: A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0) ))),"")

This will only work in Excel 2007:

=IF(B10,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1: A$9,1):INDEX(A1:A$9,B1),0)),""),"")

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty as
shown. These are a series of datasets. The information in Col B is used to
define the position and size of each dataset. For instance, B1, which is
4,
means that a dataset begins in this row and is 4 rows tall. B2, which is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one non-empty
string. In the example, the second dataset (A5:A6) is all empty strings.

I need a formula for Column C that on the first row of each dataset
returns
the non-empty string in the dataset. This formula will return an empty
string
if there are no non-empty strings in the dataset or if this row is not the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions for
us... the answers you get for questions you ask tend to be tailored to
the
conditions you say you have... if you don't tell us what those conditions
actually are, you will usually get answers that will need to be modified
when you actually do tell us what the conditions are, so you might as
well
tell them to us at the beginning and save those who you are asking to
help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula
into
A5
that will return this text entry.

Thanks,
Art

.


.



.

Biff (T Valko),

Your first formula works perfectly on the actual data.

I did not try the second formula.

You may be interested to know that the actual data contains over 8000 rows,
and there are four different column C's, for a total of 32,000 cells.

This was hugely important for my project. Thanks a lot!
--
Art


"T. Valko" wrote:

This works but seems to be overly complicated. It's the only thing I could
get to work based on every possible combination of data I tried. Shorter
formulas worked on your data as posted but when I changed the data they
crashed.

What version of Excel are you using?

This will work in all versions:

=IF(B10,LOOKUP("zzzzzz",CHOOSE({1,2},"",INDEX(A1: A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0) ))),"")

This will only work in Excel 2007:

=IF(B10,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1: A$9,1):INDEX(A1:A$9,B1),0)),""),"")

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty as
shown. These are a series of datasets. The information in Col B is used to
define the position and size of each dataset. For instance, B1, which is
4,
means that a dataset begins in this row and is 4 rows tall. B2, which is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one non-empty
string. In the example, the second dataset (A5:A6) is all empty strings.

I need a formula for Column C that on the first row of each dataset
returns
the non-empty string in the dataset. This formula will return an empty
string
if there are no non-empty strings in the dataset or if this row is not the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions for
us... the answers you get for questions you ask tend to be tailored to
the
conditions you say you have... if you don't tell us what those conditions
actually are, you will usually get answers that will need to be modified
when you actually do tell us what the conditions are, so you might as
well
tell them to us at the beginning and save those who you are asking to
help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the cells
contains
a text entry (cell A3 in this example). I want to enter a formula
into
A5
that will return this text entry.

Thanks,
Art

.


.



.

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Biff (T Valko),

Your first formula works perfectly on the actual data.

I did not try the second formula.

You may be interested to know that the actual data contains over 8000
rows,
and there are four different column C's, for a total of 32,000 cells.

This was hugely important for my project. Thanks a lot!
--
Art


"T. Valko" wrote:

This works but seems to be overly complicated. It's the only thing I
could
get to work based on every possible combination of data I tried. Shorter
formulas worked on your data as posted but when I changed the data they
crashed.

What version of Excel are you using?

This will work in all versions:

=IF(B10,LOOKUP("zzzzzz",CHOOSE({1,2},"",INDEX(A1: A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0) ))),"")

This will only work in Excel 2007:

=IF(B10,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1: A$9,1):INDEX(A1:A$9,B1),0)),""),"")

--
Biff
Microsoft Excel MVP


"Art" wrote in message
...
Ok, sorry fellas. Here is my entire issue:

_________A________B________C
1________""________4________Pear
2________""________0________""
3________Pear______0________""
4________""________0________""
5________""________2________""
6________""________0________""
7________""________3________Fig
8________Fig_______0________""
9________""________0________""

Column A contains a formula that evaluates to a string. Some are empty
as
shown. These are a series of datasets. The information in Col B is used
to
define the position and size of each dataset. For instance, B1, which
is
4,
means that a dataset begins in this row and is 4 rows tall. B2, which
is
zero, means that this row does not start a new dataset.

Each dataset may contain all empty strings or one and only one
non-empty
string. In the example, the second dataset (A5:A6) is all empty
strings.

I need a formula for Column C that on the first row of each dataset
returns
the non-empty string in the dataset. This formula will return an empty
string
if there are no non-empty strings in the dataset or if this row is not
the
first row of a dataset.

If the solution is an overly complex formula, as I suspect it is, it is
OK
to break it up into more than one column.

Thanks a lot!
--
Art


"Rick Rothstein" wrote:

For future consideration: You should **not** simplify your questions
for
us... the answers you get for questions you ask tend to be tailored to
the
conditions you say you have... if you don't tell us what those
conditions
actually are, you will usually get answers that will need to be
modified
when you actually do tell us what the conditions are, so you might as
well
tell them to us at the beginning and save those who you are asking to
help
you from having to revisit your question a second time.

--
Rick (MVP - Excel)


"Art" wrote in message
...
Yes, but the number of rows in the column will vary. That's another
problem I
have which I'm going to post now.
--
Art


"Rick Rothstein" wrote:

Given the conditions you stated, it seems this should work...

=A1&A2&A3&A4

--
Rick (MVP - Excel)


"Art" wrote in message
...
_________A
1_________
2_________
3_______Hello
4_________
5_______Hello

I have a single column range (A1:A4). One and only one of the
cells
contains
a text entry (cell A3 in this example). I want to enter a formula
into
A5
that will return this text entry.

Thanks,
Art

.


.



.