セル内で複数行に改行されている文字を、一行ずつ取得したい

セル内で、複数回改行されている文字列がある。
見やすさのためか、途中に空白行まで挟まっている。こんな感じで。

この中から、

1. ああああ
2. いいいい
3. うううう
4. ええええ

の４つの文字列を取り出したいという依頼があった。

拡張子は「.xls」だった。聞けば、昔から代々使われているファイルとのこと。
そこで、どのように改行されているか、一つずつSplit関数で分割し、その成否で
調べることにした。

改行コード	文字コード	機能
vbCr	chr(13)	キャリッジリターン	カーソルを先頭に
vbLf	chr(10)	ラインフィード	次の行へ
vbCrLf	chr(13)+chr(10)	上記組合せ
vbNewLine	chr(13)+chr(10)	上記組合せ	※Windowsの場合

例えば、こんな感じだ。普通は無いと思うが、意地悪テストで、改行コードを混在させてみた。

Sub Hoge()
    Dim Str As String
        Str = "ああああ" & vbCr & _
              "いいいい" & vbLf & vbLf & _
              "うううう" & vbCrLf & _
              "ええええ" & vbNewLine
              
        Cells(1, 1).Resize(, 5) = Split(Str, vbCr)
        Cells(2, 1).Resize(, 5) = Split(Str, vbLf)
        Cells(3, 1).Resize(, 5) = Split(Str, vbCrLf)
        Cells(4, 1).Resize(, 5) = Split(Str, vbNewLine)
End Sub

結果は、以下のとおり。当たり前と言えば、当たり前で、常にどれかを分割しそこなっている。

それにしても、毎回同じようなことを調べている。ならば、これをスッキリ分割できれば、都度調べる必要もないのではないか。

ということで、正規表現で改行コードを「,」に置き換えたうえで、分割してみた。

シンボル	機能
\n	改行にマッチ
\f	vbLfにマッチ
\r	vbCrにマッチ

それぞれ一つずつで３通り。
それぞれ二つずつで３通り。
３つ全部で１通り。
計７通りで確認した。

Sub Fuga()
    Dim Str As String
        Str = "ああああ" & vbCr & _
              "いいいい" & vbLf & vbLf & _
              "うううう" & vbCrLf & _
              "ええええ" & vbNewLine
        
    Dim myReg As Object
    Set myReg = CreateObject("VBScript.RegExp")
        myReg.Global = True
        
    Dim temp As String
        
        myReg.Pattern = "[\n]+"
        temp = myReg.Replace(Str, ",")
        Cells(5, 1).Resize(, 5) = Split(temp, ",")

        myReg.Pattern = "[\r]+"
        temp = myReg.Replace(Str, ",")
        Cells(6, 1).Resize(, 5) = Split(temp, ",")

        myReg.Pattern = "[\f]+"
        temp = myReg.Replace(Str, ",")
        Cells(7, 1).Resize(, 5) = Split(temp, ",")
        
        myReg.Pattern = "[\n\r]+"
        temp = myReg.Replace(Str, ",")
        Cells(8, 1).Resize(, 5) = Split(temp, ",")

        myReg.Pattern = "[\n\f]+"
        temp = myReg.Replace(Str, ",")
        Cells(9, 1).Resize(, 5) = Split(temp, ",")

        myReg.Pattern = "[\r\f]+"
        temp = myReg.Replace(Str, ",")
        Cells(10, 1).Resize(, 5) = Split(temp, ",")

        myReg.Pattern = "[\n\r\f]+"
        temp = myReg.Replace(Str, ",")
        Cells(11, 1).Resize(, 5) = Split(temp, ",")
End Sub

結果は、以下のとおり。

今回は \nと \r の組合せ、および３つ全てで、改行コードが混在する文字列を分割することが出来た。

以上を踏まえたうえで、作成したのがこちら。もともと「，」がある場合を考え、最初の置き換えをvbNewLineにしてみた。

Function GetSplitLineArray(source As String) As Variant
    Dim myReg As Object
    Set myReg = CreateObject("VBScript.RegExp")
        myReg.Global = True
        myReg.Pattern = "[\n\r]+"
    Dim tempStr As String
        tempStr = myReg.Replace(source, vbLf)

        GetSplitLineArray = Split(tempStr, vbLf)
End Function

テストはこちら。

Sub test()
    Dim Str As String
        Str = "ああああ" & vbCr & _
              "いいいい" & vbLf & vbLf & _
              "うううう" & vbCrLf & _
              "ええええ" & vbNewLine
        MsgBox Join(GetSplitLineArray(Str), vbNewLine)
End Sub

暫く運用してみて、問題が出たらまた改善することにしよう。

参考まで。