VBAで文字列を改行コードで分割する｜ Excel作業をVBAで効率化

文字列を改行コードで分割するには

文字列を分割するにはSplit関数を利用します。

改行コードもSplit関数で指定することが可能です。

改行コードにはCR、LF、CRLFの3種類がありますが、Windowsで扱うのはほとんどの場合がCRLFです。

あとはLinuxやMacOSXなどのUnixi系OSのテキストデータを扱うときにLFを扱います。CRは古いMacOS9以前で扱われていましたが、現在では扱うことはほとんどありません。

VBAでの改行コードの指定方法

一般的に改行コードは正規表現での書き方を踏襲していることが多く、CRは”¥r”、LFは”¥n”、CRLFは”¥r¥n”で表現します。

しかしVBAでは正規表現での書き方は行わず、改行コード用の定数が用意されており、vbCr、vbLf、vbCrLfを使います。

頭に「vb」がついているだけで、あとは名前の通りです。

vbNewLineは使用しない

vbCr、vbLf、vbCrLfの他に、4つ目のvbNewLineというVBA独自の定数もありますが、vbNewLineは使わない方がいいです。というのも、vbNewLineはMacOSXの場合に正しく動作しないためです。

vbNewLineは使っているOSに合わせて改行コードを切り替えてくれる定数で、Windowsの場合は正しくCRLFとして扱われますが、MacOSXの場合は本来Unix系のためLFとして扱われるべきですがCRとして扱われます。これは過去のMacのOS9などのときがCRだったため、その名残だと思われますが、結果的には正しく変換されません。

そういう中途半端というかバグがある定数のため、わざわざvbNewLineを使っていたりすると、この仕様を知っている別の人がそのコードを見たときに、意図的にvbNewLineを使っているのかどうか判断に迷うことがありえます。

よって、vbNewLineは使わない方がいいです。

Windowsの標準的な改行コードCRLFを分割する

Windowsでは改行コードにはCRLFが設定されます。

CRLFは名前の通り、CRとLFをくっつけた文字列で、CRの1バイトとLFの1バイトの合計2バイトの文字列になります。

以下のコードはCRLFを含む文字列をSplit関数を使ってCRLFで分割して配列に変換し、配列をループして分割した各文字列をイミディエイトウィンドウに出力します。

Sub SplitCrLfTest()
    Dim s   '// 文字列
    Dim v   '// 分割後の配列
    Dim a   '// 配列の要素
    
    '// CRLFで区切られた文字列を作成（カンマをCRLFに置換して作成）
    s = "aaa,bbb,ccc,ddd,eee"
    s = Replace(s, ",", vbCrLf)
    
    '// 文字列をCRLFで分割
    v = Split(s, vbCrLf)
    
    '// 分割した配列をループ
    For Each a In v
        '// 配列要素を出力
        Debug.Print a
    Next
End Sub

Sub SplitCrLfTest()

Dim s '// 文字列

Dim v '// 分割後の配列

Dim a '// 配列の要素

'// CRLFで区切られた文字列を作成（カンマをCRLFに置換して作成）

s = "aaa,bbb,ccc,ddd,eee"

s = Replace(s, ",", vbCrLf)

'// 文字列をCRLFで分割

v = Split(s, vbCrLf)

'// 分割した配列をループ

For Each a In v

'// 配列要素を出力

Debug.Print a

End Sub

実行結果
aaa
bbb
ccc
ddd
eee

セル内で改行されているデータを扱う場合の改行コード

上記の通り、Windowsのテキストデータを扱う場合の改行コードはCRLFになりますが、これがExcelのデータになると話が変わります。

よくある落とし穴として、シートのセルの中で改行している場合ですが、セル内での改行はCRLFではなくLFで改行されています。

当然、その場合にセル文字列をSplit関数を使って改行コードで分割させたい場合は、LFで分割しなければなりません。

以下のコードはセル内改行の文字列を取得して改行コードで分割する場合のサンプルです。

Sub CellDivTest()
    Dim s
    Dim v
    Dim a
    
    ActiveCell.Value = "aaa" & vbCrLf & "bbb" & vbLf & "ccc"
    
    '// セル文字列を取得
    s = ActiveCell.Value
    
    '// CRを除去
    s = Replace(s, vbCr, "")
    
    '// セル内の改行コードで分割
    v = Split(s, vbLf)
    
    For Each a In v
        Debug.Print a
    Next
End Sub

Sub CellDivTest()

Dim s

Dim v

Dim a

ActiveCell.Value = "aaa" & vbCrLf & "bbb" & vbLf & "ccc"

'// セル文字列を取得

s = ActiveCell.Value

'// CRを除去

s = Replace(s, vbCr, "")

'// セル内の改行コードで分割

v = Split(s, vbLf)

For Each a In v

Debug.Print a

End Sub

実際にはほとんどないとは思いますが、セル内の改行が改行コードLFではなくCRLFになっている場合があります。

それは、上のコードのようにVBAでセルに改行ありで文字列を設定した場合です。

上のコードもわざと間違って、本来LFで改行するところをCRLFで改行している箇所があります。

このコードを実行されたあとにシートを見ても、見た目上は普通のセル内改行としてしか見えませんが、データとしてはCRが混入している状態になっています。

このような誤ったCRの混入を避けるために、上のコードではセルの文字列を取得した直後に、改行コードCRをReplace関数を使って空文字列に置換して、改行コードがLFだけの状態にしています。