関数辞典

SPLIT関数の使い方｜1つのセルを区切り文字で分割する

執筆者:

admin

カテゴリ:

SPLITは、1つのセルの文字列を、区切り文字で複数のセルに分割する関数です。

「東京都新宿区西新宿」を都道府県と市区町村に分ける、カンマ区切りのデータを列に展開する、といった場面で使います。

Excelにはありません。 Excelでは「区切り位置」機能か TEXTSPLIT（365のみ）を使います。

書式

=SPLIT(文字列, 区切り文字, [各文字で区切るか], [空のセルを削除するか])
引数 内容
文字列 分割する対象
区切り文字 区切りに使う文字
各文字で区切るか 省略時 TRUE。区切り文字の1文字ずつで区切る
空のセルを削除するか 省略時 TRUE

第3引数の挙動が独特なので、後で詳しく説明します。

基本の使い方

A1に 東京,大阪,名古屋 が入っているとします。

=SPLIT(A1, ",")

結果： 3つのセルに「東京」「大阪」「名古屋」が横方向に展開されます。

結果は右方向に広がります。 右隣にデータがあると #REF! になるので、空けておいてください。

第3引数の落とし穴

区切り文字を複数指定したときの挙動が独特です。

A1に 東京-大阪_名古屋 が入っているとします。

=SPLIT(A1, "-_")

結果： 東京 / 大阪 / 名古屋

"-_" という2文字の並びではなく、「-」と「_」のどちらでも区切るという意味になります。これが第3引数の既定（TRUE）の挙動です。

「-_」という2文字の並びで区切りたいなら、第3引数を FALSE にします。

=SPLIT(A1, "-_", FALSE)

複数文字の区切り（", " や " - " など）を使うときは、必ず FALSE を指定してください。 ここを忘れると、スペースでも区切られてしまいます。

連続した区切りの扱い

A1に 東京,,名古屋 が入っている場合（大阪が欠損）。

=SPLIT(A1, ",")        → 東京 / 名古屋   （2つ）
=SPLIT(A1, ",", TRUE, FALSE)  → 東京 / （空） / 名古屋   （3つ）

第4引数を FALSE にすると、空の要素を保持します。

列の位置を揃えたいときは FALSE が必須です。既定のままだと、欠損がある行だけ列がずれます。CSVを扱うときに必ず問題になる箇所です。

特定の位置だけ取り出す

SPLITの結果から1つだけ欲しいときは、INDEX で包みます。

=INDEX(SPLIT(A1, ","), 1, 2)

「1行目・2番目」＝2つ目の要素です。

この形なら結果が1セルに収まるので、右方向に展開されません。表の中で使うときはこちらが便利です。

全行に適用する

SPLITはARRAYFORMULAと相性が悪く、複数行をまとめて分割することはできません。

行ごとに数式を置くか、次のように INDEX と組み合わせて列ごとに書きます。

B2:  =ARRAYFORMULA(IF(A2:A="", "", INDEX(SPLIT(A2:A, ","), 0, 1)))

環境によって挙動が安定しないため、確実にやるなら数式を各行にコピーするか、REGEXEXTRACT を使ってください。

=ARRAYFORMULA(IF(A2:A="", "", REGEXEXTRACT(A2:A, "^([^,]+)")))

正規表現なら ARRAYFORMULA が確実に効きます。

縦に並べたい

SPLITの結果は横に出ます。縦にしたいなら TRANSPOSE で包みます。

=TRANSPOSE(SPLIT(A1, ","))

複数行をまとめて縦一列にするなら FLATTEN を使います。

実用例

氏名を姓と名に分ける

=SPLIT(A1, " ")

全角スペースの場合は "　" を指定してください。両方に対応するなら第3引数を TRUE のままにして両方渡します。

=SPLIT(A1, " 　")

半角スペースと全角スペースのどちらでも区切られます。

メールアドレスのドメインを取り出す

=INDEX(SPLIT(A1, "@"), 1, 2)

URLからドメインを取り出す

=INDEX(SPLIT(A1, "/"), 1, 3)

https://example.com/page を / で区切ると、1番目が https:、2番目が空（既定では削除される）、3番目が example.com です。第4引数の扱いで位置が変わるので、実際の結果を確認してください。

エラーの対処

#REF! が出る

結果の展開先にデータが入っています。 SPLITは右方向に広がるので、右隣を空けてください。

分割されない

  • – 区切り文字が実際のデータと違う（全角カンマ ， と半角カンマ ,）
  • – 区切り文字がセル内に存在しない

LEN() で文字数を確認するか、SUBSTITUTE で置き換えてから分割すると解決することがあります。

意図しない場所で区切られる

第3引数が既定の TRUE のままで、複数文字の区切りを指定している可能性があります。FALSE にしてください。

列がずれる

第4引数を FALSE にして、空の要素を保持してください。

まとめ

  • – 書式は =SPLIT(文字列, 区切り文字)。結果は右方向に展開
  • – 複数文字の区切りを使うなら第3引数を FALSE
  • – 列の位置を揃えたいなら第4引数を FALSE
  • – 1つだけ取り出すなら INDEX(SPLIT(...), 1, n)
  • – 全行に適用するなら REGEXEXTRACT のほうが確実
  • – Excelには無い（「区切り位置」機能か TEXTSPLIT）

関連する関数

コメント

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

投稿をさらに読み込む