1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

testing cells with text in array formula

Discussion in 'Ask an Excel Question' started by jb, Aug 9, 2018.

  1. jb

    jb Member

    Messages:
    134
    I have an excel sheet test_blank which contains list from b5 to b15 with empty cells in between. These empty cells have "" values.

    I have an array formula in c5 to c15 which is written to generate a list without blank cells.
    Means c5 has value aa, c6 has bb, c7 must have cc, c8 must have dd and so on.

    My formula is not working in c5 to c15. I have tried to apply istext with len function. But it does not work.

    I checked formula with list of values where empty cells does not have "". It works. I have shown data in e5 to e15 and formula in f5 to f15.

    I have attached sample sheet. My original sheet will contain a list of values where empty cell will contain either text or "".

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,853
    1] Range B5:B15 contain non-breaking spaces, maybe copied from the external source (HTML).

    2] Try in C5, copied down :

    =IFERROR(INDEX($B$5:$B$15,AGGREGATE(15,6,ROW($B$1:$B$11)/(CODE($B$5:$B$15)>96),ROW(B1))),"")

    Regards
    Bosco
  3. jb

    jb Member

    Messages:
    134
    Sir it is not working.
    I have used following array formula. It works perfectly if the cell does not contain anything.

    =IFERROR(INDEX($B$5:$B$15,SMALL(IF(ISTEXT($B$5:$B$15),ROW($B$1:$B$11),""),ROW(B1))),"")

    I also know the problem. ISTEXT($B$5:$B$15) is not returning false for the cells having "" as a value.

    I just want a solution so that istext return false for cells having value ""[/quote]
  4. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    286
    One problem I would note with the proposed test
    AND(ISTEXT($B$5:$B$15),LEN($B$5:$B$15>0))
    is that the AND operation aggregates all 11 rows into a single result so that you will have no output if any row is fails to meet the conditions specified.
    In more detail
    LEN($B$5:$B$15>0)
    can only evaluate as 4 or 5 depending on whether the inequality is true or false. Both 4 and 5 are treated as TRUE when used as parameters of the AND() function.

    ISTEXT($B$5:$B$15) * (LEN($B$5:$B$15)>0)

    would get your formula working.

    For interest, I have attached a workbook that implements your method as a single multicell array formula rather than a list of single-cell array formulae. I have also introduced an index column 'k' on the grounds that it less distracting than bringing ROW() function into the formula with references to cells that are not part of the solution space.

    Attached Files:

Share This Page