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

1. ## 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. ## 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. ## 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. ## 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.

5. ## 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. ## 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

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