istext

[uk747]

=IF(ISTEXT(a3),a3,IF(ISTEXT(a2),a2,"")) cant seem to get the formula to work.A3 contains a formula and A2 doesnt. If the formula in A3 shows text based on a result then I want contents of A3 in A1, if A3 is "" then show A2 and if A2 is blank then ""

 

[uk747]

thankyou and apologies for not spotting your post

 

[Sixthsense]

No issues and thanks for the feedback

 

[Dryver14]

Sixthsence,

I have run this through formula evaluator but still fail to see what the formula is doing differently to the formula with out the the LEN function,

could you please tell me what is happening and why it returns where the basic formula does not.

Thanks in anticipation.

 

[Sixthsense]

In a blank worksheet do this exercise.

Keep A1 as blank

In B1 cell
=IF(A1=1,1,"")

In C1 cell
=ISTEXT(B1)

In D1 cell
=LEN(B1)

In E1 cell
=AND(LEN(B1),ISTEXT(B1))

Now you can see B1 seems to be empty but it's not completely empty for display it's seems to be empty, but actually B1 has been turned as Text due to "" in B1 cell formula.

So C1 formula =ISTEXT(B1) is returning TRUE but when we use Len Function to determine the Number of characters it returns 0.

The E1 cell formula =AND(LEN(B1),ISTEXT(B1)) will check whether both the conditions (i.e. Condition1 and condition2) returns TRUE.

Anything Greater Than Zero will be treated as TRUE and Zero will be treated as FALSE.

So the Len Function Returns 0 which will make the AND function to return FALSE.

Hope it's clear now

 

[Peter_SSs]

Here's another possibility that I think does what you want. It would also easily adapt to a larger range.

Assuming you have Excel 2007 or later:
=IFERROR(LOOKUP(2,1/(A2:A4<>""),A2:A4),"")

For earlier versions:
=IF(ISNA(LOOKUP(2,1/(A2:A4<>""),A2:A4)),"",LOOKUP(2,1/(A2:A4<>""),A2:A4))

 

[Dryver14]

Thanks,

Great explanation.

Thats clear now,

Also i had missed out the and section of the formula when I evaluated it.

 

[Sixthsense]

Or you can ignore Len Function and write it in this way...

=IF(AND(A4<>"",ISTEXT(A4)),A4,IF(AND(A3<>"",ISTEXT(A3)),A3,IF(AND(A2<>"",ISTEXT(A2)),A2,"")))

Edit: Oops.. Peter_SSs explained this method in Post #15

 

[Dryver14]

OK you 2 .......stop it now.

 

[Peter_SSs]

Or you can ignore Len Function and write it in this way...

=IF(AND(A4<>"",ISTEXT(A4)),A4,IF(AND(A3<>"",ISTEXT(A3)),A3,IF(AND(A2<>"",ISTEXT(A2)),A2,"")))

Edit: Oops.. Peter_SSs explained this method in Post #15

I don't think I did anything about explaining that method??

In any case, if we can believe the OP ..

the result of the formula is text.

.. then there should be no need to test for text values. My understanding is that the OP's formulas in A2:A4 return "" (text) or some other text. You also can shorten a bit by testing equality, rather than inequality. So if you want to keep the nested IFs then maybe

=IF(A4="",IF(A3="",IF(A2="","",A2),A3),A4)