excelでカンマ区切りの数値を分割して別セルに貼る式

今回はEXCELを使用した内容の記事を作成しました。

対象：Excel 関数、ExcelVBA

Excelでカンマ区切りの数値を分割して別セルに貼り付ける方法はいくつかあります。
ご自身のExcelのバージョンや用途に合わせて最適な方法を選びましょう。

方法1: TEXTSPLIT関数 (Excel 365 / 2021以降)

TEXTSPLIT関数は、Excel 365および2021以降で利用できる比較的新しい関数です。
文字列を指定した区切り文字で分割し、結果を配列として返します。

横方向に分割

A1セルに以下のデータがある場合：

123,456,789

B1セルに以下の式を入力：

=TEXTSPLIT(A1, ",")

➡ B1セル、C1セル、D1セル… に自動的に分割されます。

縦方向に分割

=TEXTSPLIT(A1, ",", , TRUE)

➡ B1セル、B2セル、B3セル… に分割されます。

TEXTSPLIT関数の第3引数は、分割後に空のセルを無視するかどうかを指定します。
省略した場合、空のセルはそのまま出力されます。
第4引数にTRUEを指定すると、分割結果を縦方向に配置します。

方法2: FILTERXML関数 (Excel 2013以降)

FILTERXML関数は、XML形式の文字列からXPath式を使ってデータを抽出する関数です。
この関数を使うことで、カンマ区切りの文字列を分割することができます。

横方向に分割

=FILTERXML("<t><s>" & SUBSTITUTE(A1, ",", "</s><s>") & "</s></t>", "//s")

縦方向に分割

=TRANSPOSE(FILTERXML("<t><s>" & SUBSTITUTE(A1, ",", "</s><s>") & "</s></t>", "//s"))

FILTERXML関数を使った方法は、少し複雑な数式になります。
SUBSTITUTE関数でカンマをXMLタグに置き換え、FILTERXML関数でタグ内の文字列を抽出しています。
TRANSPOSE関数を使うことで、縦方向に分割することができます。

方法3: MID関数とSEARCH関数の組み合わせ（関数のみ）

MID関数とSEARCH関数を組み合わせることで、Excelのすべてのバージョンでカンマ区切りの文字列を分割することができます。
ただし、数式が複雑になるため、注意が必要です。

1番目の値（B1セル）

=LEFT(A1,SEARCH(",",A1)-1)

2番目の値（C1セル）

=MID(A1,SEARCH(",",A1)+1,SEARCH(",",A1&",",SEARCH(",",A1)+1)-SEARCH(",",A1)-1)

3番目の値（D1セル）

=RIGHT(A1,LEN(A1)-SEARCH(",",A1,SEARCH(",",A1)+1))

LEFT関数、MID関数、RIGHT関数を使って、カンマで区切られた文字列を順番に抽出しています。
SEARCH関数でカンマの位置を調べています。

方法4: VBAを使う（大量のデータ向け）

VBA（Visual Basic for Applications）を使うことで、大量のデータを効率的に処理することができます。

Function SplitValues(ByVal txt As String, ByVal delim As String, ByVal index As Integer) As String
    Dim arr As Variant
    arr = Split(txt, delim)
    If index <= UBound(arr) Then
        SplitValues = arr(index)
    Else
        SplitValues = ""
    End If
End Function

使い方：

B1セルに1番目の値を取得する場合：

=SplitValues(A1, ",", 0)

C1セルには：

=SplitValues(A1, ",", 1)

➡ インデックスを増やすと、別の要素を取得できます。

VBAを使う方法は、大量のデータを処理するのに適していますが、VBAの知識が必要になります。

まとめ

方法	使用可能バージョン	特徴
TEXTSPLIT	Excel 365 / 2021	簡単で便利。文字列を分割して配列で返す。
FILTERXML	Excel 2013以降	関数だけでOK。XML形式の文字列を操作する。
MID+SEARCH	すべてのバージョン	手動で対応可能。数式が複雑になる。
VBA	すべてのバージョン	大量データ向け。VBAの知識が必要。

おすすめの方法

✅ Excel 365 / 2021なら TEXTSPLIT が最も簡単です。
✅ Excel 2013-2019なら FILTERXML がおすすめです。
✅ 旧バージョンなら MID+SEARCH で対応できます。
✅ 大量のデータがある場合は、VBAを使うと効率的に処理できます。

上記の内容で、Excelでカンマ区切りの数値を分割して別セルに貼り付ける方法について、より理解を深めることができたでしょうか？
ご不明な点がありましたら、お気軽にご質問ください。