Extract all the numbers from single excel sheet

Question

Given an excel sheet with mixed number and text.

ES:

123|text|11
-----------
hi |  75| 1

How can I scan all the sheet and extract just the numbers in a single column?

123|
----
 75|
----
 11|
----
  1|

I prefer not to use VBA, order is not important.

Answer 1

It depends on the use case here but the logic I'd suggest is (without using VBa) is to copy each column into a single long column, then simply sort alphabetically!

So it starts off as

Copy and paste to a single column

Add a new row and a heading title click on Filter (in the Data ribbon, Sort and Filter tab)

Sort smallest to largest

Simply delete the other rows!

If copying all the columns into a single column is too time consuming then see https://stackoverflow.com/questions/4480227/how-to-consolidate-data-from-multiple-excel-columns-all-into-one-column

Sub MakeOneColumn()

    Dim vaCells As Variant
    Dim vOutput() As Variant
    Dim i As Long, j As Long
    Dim lRow As Long

    If TypeName(Selection) = "Range" Then
        If Selection.Count > 1 Then
            If Selection.Count <= Selection.Parent.Rows.Count Then
                vaCells = Selection.Value

                ReDim vOutput(1 To UBound(vaCells, 1) * UBound(vaCells, 2), 1 To 1)

                For j = LBound(vaCells, 2) To UBound(vaCells, 2)
                    For i = LBound(vaCells, 1) To UBound(vaCells, 1)
                        If Len(vaCells(i, j)) > 0 Then
                            lRow = lRow + 1
                            vOutput(lRow, 1) = vaCells(i, j)
                        End If
                    Next i
                Next j

                Selection.ClearContents
                Selection.Cells(1).Resize(lRow).Value = vOutput
            End If
        End If
    End If

End Sub

How do I add VBA in MS Office?

Answer 2

You can do this with a formula:

 =IFERROR(LARGE(IF(ISNUMBER(A$1:C$3),A$1:C$3),ROW()),"")

This lists the numbers from large to small:

This is an array formula, so it must be entered with CTRL-Shift Enter, rather than just Enter. If entered correctly, Excel will surround the formula with curly brackets {} in the formula bar.

To scan the whole sheet, change the ranges in the IF statement to $1:$1048576. But you'll need to exclude the column you're listing the numbers in. So you could list in Column A and use B$1:XFD$1048576. (This is for Excel after 2007).