# Solved Excel formula to skip blank cells February 22, 2010 at 11:34:40
Specs: Windows XP
 Is there a formula I can use to automatically skip blank rows and return only the values of a particular cell(s) that have numbers in them? See More: Excel formula to skip blank cells February 22, 2010 at 13:53:15
 Assuming your example data is in A1:A9, place this in B1 and drag it down to B9. It will list the values in ascending order:=SMALL(\$A\$1:\$A\$9,ROW())Now, once you run out of values (in your case - 3) you'll start to get a #NUM error.To prevent that, use ISERROR as follows:=IF(ISERROR(SMALL(\$A\$1:\$A\$9,ROW())),"",SMALL(\$A\$1:\$A\$9,ROW()))

#1 February 22, 2010 at 11:40:12
 We're going to need a little more information before we can help.An IF statement such as this will return the value in A1 if it's not empty and nothing if it is. =IF(A1<>"",A1,"")I don't if that is what you are looking for, so please give us a little more detail.

Report •

#2
February 22, 2010 at 11:48:26
 Sorry. So in short I have a column that looks like this12345(blank)(blank)12346(blank)(blank)(blank)(blank)12347and I'm trying to get it to look like this123451234612347

Report •

#3 February 22, 2010 at 11:52:06
 Why not just use Sort Ascending?

Report •

Related Solutions

#4
February 22, 2010 at 13:16:48
 I could do that, but I was trying to filter a spreadsheet into another one and I'm trying to do so all in one step.

Report •

#5 February 22, 2010 at 13:53:15
 Assuming your example data is in A1:A9, place this in B1 and drag it down to B9. It will list the values in ascending order:=SMALL(\$A\$1:\$A\$9,ROW())Now, once you run out of values (in your case - 3) you'll start to get a #NUM error.To prevent that, use ISERROR as follows:=IF(ISERROR(SMALL(\$A\$1:\$A\$9,ROW())),"",SMALL(\$A\$1:\$A\$9,ROW()))

Report •

#6
February 22, 2010 at 14:18:34
 That works! However, my row 1 is going to have a header so if I paste that formula in row 2 it excludes the first number in the original column I am referencing. I'll try and work it out. Thanks so much.

Report •

#7 February 22, 2010 at 16:10:47
 Row() returns an integer value that represents the row that the Row() function is in. Since you are starting in Row 2, you just need to adjust it a bit.In Row 2, Row() will return 2, so just use Row()-1 to have it evaluate to 1.

Report • 