• Related to the following screenshot, the formula

  =IF(ISNUMBER($A$1:$A$5),$A$1:$A$5)
  

  will evaluate to the following array

  {1;FALSE;2;44644;3}
  

  but I only need it to return the numbers

  {1;2;3}
  

• How can this be achieved (getting rid of the dates and booleans)?

• Note that ISNUMBER has already gotten rid of error values and whatnot.

Utilization in VBA

• Instead of the loop and whatnot in the first procedure I want to simplify by evaluating the correct formula in the second procedure.

Correct

Sub CorrectVBA()
' Result:
' 1
' 2
' 3
   
    Const rgAddress As String = "A1:A5"
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    Dim rg As Range: Set rg = ws.Range(rgAddress)
    Dim Data As Variant: Data = rg.Value
    
    Dim Arr() As Double
    Dim Item As Variant
    Dim r As Long
    Dim n As Long
    
    For r = 1 To UBound(Data, 1)
        Item = Data(r, 1)
        If WorksheetFunction.IsNumber(Item) Then
            If Not IsDate(Item) Then
                n = n   1
                ReDim Preserve Arr(1 To n)
                Arr(n) = Item
            End If
        End If
    Next r
       
    If n = 0 Then Exit Sub
       
    For n = 1 To UBound(Arr)
        Debug.Print Arr(n)
    Next n
       
End Sub

Wrong

• Something like this is what I want to do in this particular case.
• It is wrong because the formula is wrong.

Sub WrongVBA()
' Result:
'  1
' False
'  2
'  44644
'  3
    
    Const rgAddress As String = "A1:A5"
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    Dim Data As Variant
    Data = ws.Evaluate("IF(ISNUMBER(" & rgAddress & ")," & rgAddress & ")")
    
    Dim r As Long
    For r = 1 To UBound(Data, 1)
        Debug.Print Data(r, 1)
    Next r
   
End Sub

CodePudding user response：

365

=FILTER(A1:A5,ISNUMBER(-TEXT("1/1/"&A1:A5,"e/m/d")))

or older version:

=N(OFFSET(A1,SMALL(IF(ISNUMBER(-TEXT("1/1/"&A1:A5,"e/m/d")),ROW(1:5)-1),ROW(INDIRECT("1:"&COUNT(-TEXT("1/1/"&A1:A5,"e/m/d"))))),))

CodePudding user response：

This is not easy through formulae because Excel can not distinguish between say 44644 being an integer or meant to be a date unless you look at formatting of the cel. The only reasonable way of doing this that I can think of is to use

Formula in C1:

=FILTER(A1:A5,BYROW(SEQUENCE(5,,0),LAMBDA(a,CELL("format",OFFSET(A1,a,0))="G"))*ISNUMBER(A1:A5))

This would work to filter out the dates (given the format in your data) since the cells that are not formatted as date would return "G" ('General', see the link to the ms-documentation). My limited testing suggested that this would work to filter out dates from the equation as per the question.

Note: To be more specific, you can exclude any cells that have this specific formatting ('mm/dd/yyyy') you have shown, from the equation through:

=FILTER(A1:A5,BYROW(SEQUENCE(5,,0),LAMBDA(a,LEFT(CELL("format",OFFSET(A1,a,0)))<>"D"))*ISNUMBER(A1:A5))

EDIT: The above solution would be ms365 exclusive. For Excel 2019, one way to do this is:

=FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(ISNUMBER(A1:A5),A1:A5,""))&"</s></t>","//s")

• It's an CSE-entered formula. If done correctly the returned array would be {1;2;44644;3};
• We could also use =FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,A1:A5)&"</s></t>","//s[.*0=0]") however, since TEXTJOIN() has a limit I thought it would be wise to proces any non-number into an empty string beforehand;
• There is no way (I can think of) to exclude the dates in this version of Excel.