+ Reply to Thread
Results 1 to 6 of 6

Remove Blank Cells from a Range when blank cells are actually a result from a IF Func.

  1. #1
    Registered User
    Join Date
    02-07-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    3

    Remove Blank Cells from a Range when blank cells are actually a result from a IF Func.

    Extract a List Excluding Blank Cells using =IFERROR(INDEX($A$2:$A$10,SMALL(IF(ISTEXT($A$2:$A$10),ROW($A$1:$A$9),""),ROW(A1))),"")

    But as the blank cell is actually a result from a IF Function it doesn't seem to work.
    I can't seem to rewrite the list without the blanks I have tried using symbols such as +- instead of "". It needs to be a live document too. Have anyone got any ideas?

    Thanks Berne.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Remove Blank Cells from a Range when blank cells are actually a result from a IF Fu

    Hi,

    Rather than using the ISTEXT function, try evaluating if the length of the string is greater than zero, using the LEN function.

    Hope this helps

  3. #3
    Registered User
    Join Date
    02-07-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Remove Blank Cells from a Range when blank cells are actually a result from a IF Fu

    Ryan,

    I had a go using LEN (excuse me if this is utterly stupid as i'm new to formula) but could not get anything to copy to the new cells.

    =IFERROR(INDEX($S$2:$S11,SMALL(IF(LEN(S:S)>0,S:S,""), ROW(S1))),"")

    Does this look right?

    Berne

  4. #4
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Remove Blank Cells from a Range when blank cells are actually a result from a IF Fu

    I have tried with in two types.

    Please try this file.
    Attached Files Attached Files
    Please click 'Add reputation', if my answer helped you.

  5. #5
    Registered User
    Join Date
    02-07-2013
    Location
    london
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Remove Blank Cells from a Range when blank cells are actually a result from a IF Fu

    =+IFERROR(INDEX($S$2:$S$500,SMALL(IF($S$2:$S$500<>"",ROW($S$2:$S$500)-ROW($S$2)+1),ROWS(B$2:B2))),"")

    Worked perfectly!

    Thank you.

  6. #6
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Remove Blank Cells from a Range when blank cells are actually a result from a IF Fu

    No problems!

    Please don't forget to mark this thread as solved and click on the * next to any post that has been of assistance to you, just to say thanks to the poster for taking the time to help you

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Sort all cells in range with result which are not blank
    By spangpang in forum Excel General
    Replies: 2
    Last Post: 04-09-2013, 06:17 AM
  2. Aggregate data in a range of columns to remove blank cells
    By arbgd1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-05-2013, 04:11 PM
  3. Advanced filter with blank cells / Dynamic named range with blank cells
    By Jason_2112 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2010, 12:06 PM
  4. Remove blank cells from a range
    By Dave in forum Excel General
    Replies: 2
    Last Post: 01-04-2006, 04:35 PM
  5. Replies: 0
    Last Post: 08-23-2005, 03:43 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1