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?

✔ Best Answer

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()))

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.

Sorry. So in short I have a column that looks like this 12345

(blank)

(blank)

12346

(blank)

(blank)

(blank)

(blank)

12347and I'm trying to get it to look like this

12345

12346

12347

Why not just use Sort Ascending?

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.

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()))

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.

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.

Ask Your Question

Weekly Poll

Would you use "Spotlight Search" on Windows?

Discuss in The Lounge

Poll History