VBAで正規表現を使う

' [参照設定] Microsoft VBScript Regular Expressions 5.5

Public Function searchCCC(rng As Range) As String
    Dim re As RegExp
    Set re = New RegExp
    With re
        .Pattern = "^【(.+)】(.+)_(.+)_(.+)$"
        .IgnoreCase = False
        .Global = True
        Dim reMatch As Object
        Set reMatch = .Execute(rng)
        If reMatch.Count > 0 Then
            If reMatch(0).SubMatches.Count > 0 Then
                searchCCC = reMatch(0).SubMatches(2)
                Exit Function
            End If
        End If
    End With
    searchCCC = "Not Found."
End Function