Results 1 to 10 of 10

Thread: ISBLANK and Nested IF

  1. #1

    Question ISBLANK and Nested IF



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    I've had a bit of trouble formatting my nested IF statement. The statement is designed to take a band or artist name, then take the last word of the name and output it. If it contains a number it will output the entire name.

    The issue I'm having is when the formula is applied to empty cells (it needs to be there for the client, who do not have much Excel knowledge) - it returns a zero which is fair enough and I can remove this via formatting but that does not allow me to sort the data properly because it detects the zero cells.

    To counter this I'm trying to use ISBLANK to remove the zeroes properly but I can't seem to get it to format properly.

    What I'm currently using:

    Code:
    =IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)),"")
    What I'm trying to use:

    Code:
    =IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2,IF(ISBLANK(B2),""),"")

    Sample of A2,A3,A4:

    Code:
    JESSE JACKSON
    JACKSON 5
    CHI-LITES
    Output B2,B3,B4:

    Code:
    JACKSON
    JACKSON 5
    CHI-LITES

    Thanks!!
    Last edited by alexander21; 2014-01-09 at 04:06 PM.

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    How about, checking if A2 is blank first?

    e.g

    =IF(A2="","",IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)),""))


  3. #3
    Quote Originally Posted by NBVC View Post
    How about, checking if A2 is blank first?

    e.g

    =IF(A2="","",IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)),""))
    That looks like it should work, thanks! however now I'm faced with the error of "too many levels of nesting than the file type allows" despite saving in a .xlsx format

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    You can probably remove the IFERROR() now, I don't think you will need it.

    or you can change formula a bit too:

    =IF(A2="","",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)))

    or even

    =IF(A2="","",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),A2)))


  5. #5
    Quote Originally Posted by NBVC View Post
    You can probably remove the IFERROR() now, I don't think you will need it.

    or you can change formula a bit too:

    =IF(A2="","",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)))

    or even

    =IF(A2="","",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),A2)))
    That's perfect, thank you!!

    However it didn't fix the core problem, the zeros (even though they're not ''really'' there) still get counted when using the column to sort, which results in a bunch of empty rows at the top of the spreadsheet, I want them to not be sorted at all (and as a result end up at the bottom of the spreadsheet).
    Last edited by alexander21; 2014-01-11 at 12:36 PM.

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    If you put column A first in the sort order, the blank cells appear at the bottom (with an A to Z sort)

  7. #7
    Quote Originally Posted by Hercules1946 View Post
    If you put column A first in the sort order, the blank cells appear at the bottom (with an A to Z sort)
    That is correct, but the whole idea of the formula is to make the sort on Column B more accurate by providing last names etc. Sorting Column A or C would make the formula pointless

  8. #8
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by alexander21 View Post
    That is correct, but the whole idea of the formula is to make the sort on Column B more accurate by providing last names etc. Sorting Column A or C would make the formula pointless
    Obviously you have the advantage as I haven't seen your data, but my idea was to sort by col A, and then by Col B therefore getting all the blanks to the bottom
    and then sort again by Col B excluding the blank rows.

  9. #9
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,437
    Articles
    0
    Excel Version
    Excel 2016
    You could change formula to:

    =IF(A2="","zzzzz",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),A2)))

    then conditionally format the column to hide the zzzzz then they will sort to the bottom.
    Last edited by NBVC; 2014-01-14 at 01:56 PM.


  10. #10
    Quote Originally Posted by NBVC View Post
    You could change formula to:

    =IF(A2="","zzzzz",IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(ISNUMBER(SEARCH(" ",A2)),TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100)),A2)))

    then conditionally format the column to hide the zzzzz then they will sort to the bottom.
    The column is generally always hidden anyway so I wouldn't even need to hide the zzzzz, this works though so thank you very much and thank you to everyone else.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •