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

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

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

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

対象は、こんな感じだ。

  • 1行目:会社名
  • 2行目:案件管理番号(アルファベット1文字+数字6文字)
  • 3行目:担当者名

例えば、↓ このセルから「A142857」を取得したい訳だ。
f:id:Infoment:20190215215953p:plain


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

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

f:id:Infoment:20190215220740p:plain


これは上手くいったか?と思いきや、こんなケースが出てきた。管理番号の後ろに( )で補足情報が書かれているものがあったのだ。
f:id:Infoment:20190215221113p:plain
欲しいのは、管理番号だけ。(仮)は必要ない。

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

Aチームの場合

管理番号が固定長であることを利用して、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

f:id:Infoment:20190215221627p:plain

Bチームの場合

補足情報が"("から始まることに着目して、もう一回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

f:id:Infoment:20190215221851p:plain

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

その他の例

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

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

f:id:Infoment:20190215223231p:plain
ちょっと大仰だな。正規表現、使うまでも無いか。

参考まで。