Given an excel sheet with mixed number and text.


hi |  75| 1

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


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


You can do this with a formula:


This lists the numbers from large to small:

enter image description here

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

  • Whoa, I needed 20 minutes to understand it. – Giulio Caccin Aug 21 '17 at 19:10
  • Sorry, I usually write an explanation, but I had to go look at the eclipse. :-D – Bandersnatch Aug 21 '17 at 19:13

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

enter image description here

Copy and paste to a single column

enter image description here

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

Sort smallest to largest

enter image description here

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.Cells(1).Resize(lRow).Value = vOutput
            End If
        End If
    End If

End Sub

How do I add VBA in MS Office?

  • I can also use a ISNUMBER but still this approach is too manual :( I need to scan all the sheet... – Giulio Caccin Aug 21 '17 at 13:12
  • You have not defined that in your question though... – Dave Aug 21 '17 at 13:13
  • I'm sorry i'll update the question – Giulio Caccin Aug 21 '17 at 13:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.