用VBA

Sub find_cha()
    Dim myRng As Range, allRng As Range, i As Long
    Dim firstAddress As String
    
    Set allRng = Application.Intersect(Range("A:A"), Cells.Parent.UsedRange)
    Set myRng = allRng.Find(what:="甲", LookAt:=xlPart)
    If myRng Is Nothing Then Exit Sub
    
    firstAddress = myRng.Address
    i = 0
    Do
        i = i + 1
        Cells(i, "C") = myRng.Value
        Set myRng = allRng.FindNext(myRng)
    Loop Until myRng.Address = firstAddress
End Sub

我的非專業做法是用=IF(ISERROR(SEARCH("甲",A1)),"",A1)
然後排序結果那一欄

在C1輸入
=IFERROR(INDEX(A:A,SMALL(IF(IFERROR(FIND("甲",A:A,1),0),ROW(A:A),""),ROW(A1))),"")
輸入後不要按Enter
要按Ctrl+Enter(使之成為陣列函數)
再向下複製(拖拉複製)即可