VBAで前株等をとる関数を作成

社名の横に数式を入れると、まずは（株）等を取り出した会社名、次の列に（株）等を表示させます。

Function ExtractNameWithLog(ByVal txt As String) As Variant
    Dim s As String
    s = txt

    Dim kinds As Variant
    Dim k As Variant
    Dim log As String
    Dim note As String

    ' 除去する法人格（元表記）
    kinds = Array( _
        "株式会社", "㈱", "（株）", "(株)", _
        "合同会社", _
        "有限会社", "㈲", "（有）", "(有)", _
        "医療法人", "社会福祉法人" _
    )

    log = ""
    note = ""

    ' --- 法人格除去＋ログ ---
    For Each k In kinds
        If InStr(s, k) > 0 Then
            s = Replace(s, k, "")
            If log <> "" Then log = log & "、"
            log = log & k
        End If
    Next k

    ' --- 全角カッコ ---
    If Left(s, 1) = "（" Then
        If InStr(s, "）") > 0 Then
            note = "（" & Mid(s, 2, InStr(s, "）") - 2) & "）"
            s = Mid(s, InStr(s, "）") + 1)
        End If
    End If

    If Left(s, 1) <> "（" Then
        If InStr(s, "（") > 0 Then
            note = "（" & Mid(s, InStr(s, "（") + 1, InStr(s, "）") - InStr(s, "（") - 1) & "）"
            s = Left(s, InStr(s, "（") - 1)
        End If
    End If

    ' --- 半角カッコ ---
    If Left(s, 1) = "(" Then
        If InStr(s, ")") > 0 Then
            note = "（" & Mid(s, 2, InStr(s, ")") - 2) & "）"
            s = Mid(s, InStr(s, ")") + 1)
        End If
    End If

    If Left(s, 1) <> "(" Then
        If InStr(s, "(") > 0 Then
            note = "（" & Mid(s, InStr(s, "(") + 1, InStr(s, ")") - InStr(s, "(") - 1) & "）"
            s = Left(s, InStr(s, "(") - 1)
        End If
    End If

    ' --- ログ統合 ---
    If note <> "" Then
        If log <> "" Then
            log = log & "、" & note
        Else
            log = note
        End If
    End If

    ExtractNameWithLog = Array(Trim(s), log)
End Function