Find and Return the Non-Blank entry

[Art]

_________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

 

[T. Valko]

Try this...

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

=LOOKUP("zzzzzz",A1:A4)

 

[Paul C]

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

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

 

[Mike H]

Art,

Try this

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

Mike

 

[Mike H]

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

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

Mike

 

[Rick Rothstein]

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

=A1&A2&A3&A4

 

[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

 

[Rick Rothstein]

I think mine does.

--
Rick (MVP - Excel)

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

 

[Mike H]

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]

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]

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

 

[Mike H]

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

 

[T. Valko]

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))

 

[Rick Rothstein]

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.

 

[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!

 

[T. Valko]

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(B1>0,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(B1>0,IFERROR(INDEX(A1:A$9,MATCH("?*",INDEX(A1:A$9,1):INDEX(A1:A$9,B1),0)),""),"")

 

[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]

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!

 

[T. Valko]

You're welcome. Thanks for the feedback!