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.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityGiven 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.
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).
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