セル内で２回改行された真ん中の行だけを抜き取る

職場で、こんな質問を受けた。

セル内で２回改行された真ん中の行だけを抜き取りたいんだけど、どうすればいい？

そこで、職場の勉強会でテーマにしてみた。

対象は、こんな感じだ。

• １行目：会社名
• ２行目：案件管理番号（アルファベット１文字＋数字６文字）
• ３行目：担当者名

例えば、↓ このセルから「Ａ１４２８５７」を取得したい訳だ。

二チームに分かれて皆さん色々と工夫していたが、こちらに落ち着きつつあった。

Sub test()
    Dim val As String
        val = Range("A1").Value
        
    ' Split関数で、改行コードを区切り文字として配列化し、
    ' 二つ目の要素を案件管理番号として取得する。
    Dim ControlNumber As String
        ControlNumber = Split(val, vbLf)(1)
        MsgBox ControlNumber
End Sub

これは上手くいったか？と思いきや、こんなケースが出てきた。管理番号の後ろに（　）で補足情報が書かれているものがあったのだ。

欲しいのは、管理番号だけ。（仮）は必要ない。

ここで二チーム、考え方が分かれた。

Ａチームの場合

管理番号が固定長であることを利用して、LEFT関数で（　）より前を取得。

Sub test_teamA()
    Dim val As String
        val = Range("A1").Value
        
    ' Split関数で、改行コードを区切り文字として配列化し、
    ' 二つ目の要素を案件管理番号として取得する。
    Dim ControlNumber As String
        ControlNumber = Left(Split(val, vbLf)(1), 7)
        MsgBox ControlNumber
End Sub

Ｂチームの場合

補足情報が"（"から始まることに着目して、もう一回Split。

Sub test_teamB()
    Dim val As String
        val = Range("A1").Value
        
    ' Split関数で、改行コードを区切り文字として配列化し、
    ' 二つ目の要素を案件管理番号として取得する。
    Dim ControlNumber As String
        ControlNumber = Split(Split(val, vbLf)(1), "（")(0)
        MsgBox ControlNumber
End Sub

結果として、どちらも上手くいった。複数人で考えると、様々なアイディアが出て面白い。

その他の例

「みんな大好きな『正規表現』はどう？」と提案したところ、即却下された。でも折角考えたので、ここに置いておこう。

Sub test_teamC()
    Dim val As String
        val = StrConv(Range("A1").Value, vbNarrow)
        
    Dim myReg As RegExp
    Set myReg = New RegExp
        myReg.Pattern = "^.+\n([A-Z]\d{6}).*\n.+"
    Dim mc As MatchCollection
    Dim sm As SubMatches
    
    Dim ControlNumber As String
        If myReg.test(val) Then
            Set mc = myReg.Execute(val)
            Set sm = mc(0).SubMatches
                ControlNumber = sm(0)
                MsgBox ControlNumber
        End If
End Sub

ちょっと大仰だな。正規表現、使うまでも無いか。

参考まで。