Formula To Only List Numbers

[CRS111]

Hello


I have a list of information that has a mix of cells with numbers and words and I am looking for a formal to list only the cell with numbers.

As Below

10	10
DAY	30
30	20
DOG
20

However I need the vales in the second column to update if the values change in the first column.


thanks For your help

[Special-K]

in B1
=IFERROR(INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100),ROW($A$1:$A$100)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter

and copy down for as many rows as you have in column A

[JohnTopley]

Try

=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER($A$2:$A$100),ROW($A$2:$A$100),""),ROWS($A$2:A2))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

[AlKey]

Here is a regular formula

Please Login or Register  to view this content.

and this regular is for users of Excel 2010 and up

Please Login or Register  to view this content.

[XOR LX]

If your dataset is in fact quite large, I recommend avoiding potentially resource-heavy IFERROR set-ups (see here for an explanation).

Use a single helper cell somewhere within the worksheet to calculate the number of numerics within the range, and reference that within the main formulas. For example, using C1 for this purpose, we would enter, in that cell:

=COUNT(A1:A100)

The main array formula is then:

=IF(ROWS($1:1)>C$1,"",INDEX(A:A,SMALL(IF(ISNUMBER(A$1:A$100),ROW(A$1:A$100)),ROWS($1:1))))

What's more, if the order of returns of those numbers does not have to correspond with their order within the original list, simply:

=SMALL(A$1:A$100,ROWS($1:1))

which will be far, far more efficient than the above.

Regards

[Special-K]

If your dataset is in fact quite large, I recommend avoiding potentially resource-heavy IFERROR set-ups (see here for an explanation).

Use a single helper cell somewhere within the worksheet to calculate the number of numerics within the range, and reference that within the main formulas. For example, using C1 for this purpose, we would enter, in that cell:

=COUNT(A1:A100)

The main array formula is then:

=IF(ROWS($1:1)>C$1,"",INDEX(A:A,SMALL(IF(ISNUMBER(A$1:A$100),ROW(A$1:A$100)),ROWS($1:1))))

What's more, if the order of returns of those numbers does not have to correspond with their order within the original list, simply:

=SMALL(A$1:A$100,ROWS($1:1))

which will be far, far more efficient than the above.

Regards

This is still an array formula then?

[XOR LX]

This is still an array formula then?

Which one?

Regards

[Special-K]

The IF ROWS... you posted.

Looks like it is.
Nice alternative.

[XOR LX]

This is still an array formula then?

The main array formula is then:

=IF(ROWS($1:1)>C$1,"",INDEX(A:A,SMALL(IF(ISNUMBER(A$1:A$100),ROW(A$1:A$100)),ROWS($1:1))))

Indeed it is!