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.

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

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

=+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.

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