Remove Blanks from a Column with Formula

[IainRourke]

Hi,

I have a column of data like this:

(COLUMN B)
0
41.44930565
29.71363323
29.71363323



60.26418424
29.71363323
29.71363323



55.97371796
55.97371796

Where the empty cells are actually "" (returned from a previous formula).
What combination of functions should I use if I want to remove the ""s from this list and return the result in a new column? Column C should look like:

(COLUMN C)
0
41.44930565
29.71363323
29.71363323
60.26418424
29.71363323
29.71363323
55.97371796
55.97371796

I think I need to use a combination of INDEX, MATCH and COUNTIF functions, but I don't know the order in which to use them.

Thanks in advance,

Iain

 

[Mike Szczesny]

Can you use something like this? Assume my data range is A1:A7 In another cell place =[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($C$1:C1)))," ")

Enter with Cntrl+Shift+Enter[/TD]
[/TR]
</tbody>[/TABLE]

 

[KyleX]

Can you use something like this? Assume my data range is A1:A7 In another cell place =[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7<>"",ROW($A$1:$A$7)-ROW($A$1)+1),ROWS($C$1:C1)))," ")

Enter with Cntrl+Shift+Enter[/TD]
[/TR]
</tbody>[/TABLE]

I like it. Creative solution.