スプレッドシートのTEXTSPLIT関数の使い方｜区切りで行列分割

スポンサーリンク

TEXTSPLIT関数の基本（スプレッドシート）

Googleスプレッドシートで「カンマ区切りのデータを列に分けたい」と思ったことはありませんか? 改行で区切られたデータを行に展開したい場面もありますよね。

SPLIT関数なら列方向への分割はできます。でも行方向には分割できなくて困った経験がある方もいるのではないでしょうか。

そんなときに使えるのがTEXTSPLIT関数です。列方向だけでなく行方向にも分割できます。両方を同時に指定すれば2次元の表も一発で作れますよ。

この記事ではTEXTSPLIT関数の基本から、SPLIT関数との違い、TEXTJOINとの相互変換まで解説します。

読み方と語源

TEXTSPLIT関数の読み方は「テキストスプリット」です。「TEXT（文字列）」+「SPLIT（分割する）」で「文字列を分割する関数」という意味です。

TEXTSPLIT関数はExcel（Microsoft 365）にも搭載されている関数です。スプレッドシートとExcelの両方で同じ構文で使えるので、どちらのツールでも活用できますよ。

構文と引数の説明

TEXTSPLIT関数の構文はこちらです。

=TEXTSPLIT(テキスト, 列の区切り, [行の区切り], [空を無視], [大小区別], [埋め値])
引数必須/省略可説明
テキスト必須分割したい文字列またはセル参照
列の区切り必須列方向（右方向）の区切り文字。行のみ分割する場合は””を指定
行の区切り省略可行方向（下方向）の区切り文字
空を無視省略可TRUE: 空の結果を除外 / FALSE（デフォルト）: 空セルも保持
大小区別省略可0（デフォルト）: 大文字小文字を区別 / 1: 区別しない
埋め値省略可行列分割で要素数が揃わないときに埋める値（デフォルトは#N/A）

最低限必要なのはテキストと列の区切りの2つです。行方向の分割が不要なら、SPLIT関数と同じ感覚で使えますよ。

TEXTSPLIT関数の基本的な使い方

まずはシンプルな列方向の分割から見てみましょう。A1に「東京,大阪,名古屋,福岡」と入っているとします。

=TEXTSPLIT(A1, ",")

結果はこうなります。

B1C1D1E1
東京大阪名古屋福岡

カンマを区切り文字に指定するだけで、4つの都市名が右方向に展開されました。ここまではSPLIT関数と同じ動きですね。

スペース区切りのデータも同じ要領で分割できます。

=TEXTSPLIT("田中 太郎", " ")
→ 「田中」と「太郎」が2つのセルに分かれる

フルネームを姓と名に分けたいときに便利ですよ。

TEXTSPLIT関数で行方向に分割する

TEXTSPLIT関数の最大の特長は行方向（下方向）にも分割できることです。これはSPLIT関数にはない機能ですよ。

行方向に分割するには、第2引数（列の区切り）を空文字列にして、第3引数（行の区切り）に区切り文字を指定します。

A1に「りんご/みかん/ぶどう」と入っているとします。

=TEXTSPLIT(A1, "", "/")

結果はこうなります。

A1の数式結果
りんご
みかん
ぶどう

データが下方向に展開されました。列方向の区切りを空文字列””にするのがポイントです。省略するとエラーになるので注意してくださいね。

改行コード（CHAR(10)）を区切り文字にすれば、セル内改行で区切られたデータも行に分割できます。

=TEXTSPLIT(A1, "", CHAR(10))

1つのセルに改行で詰め込まれたデータを、行ごとに展開したいときに重宝しますよ。

行と列の両方向にTEXTSPLIT関数で分割する

第2引数と第3引数を同時に指定すると、2次元の表を一発で作れます。これがTEXTSPLIT関数のいちばんの強みです。

A1に以下のようなデータが入っているとします。

東京,100,個/大阪,200,箱/名古屋,150,個

スラッシュで行を分け、カンマで列を分けたい場合はこう書きます。

=TEXTSPLIT(A1, ",", "/")

結果はこうなります。

 BCD
1東京100個
2大阪200箱
3名古屋150個

1つのセルのデータが3行3列の表に展開されました。CSVデータを一気に表形式にしたいときに最適です。

pad_with引数で#N/Aを防ぐ

行列分割で各行の要素数が異なると、不足部分に#N/Aが表示されます。

=TEXTSPLIT("東京,100/大阪", ",", "/")
 BC
1東京100
2大阪#N/A

2行目は要素が1つしかないため、C列が#N/Aになります。これを避けるには第6引数（pad_with）で埋め値を指定しましょう。

=TEXTSPLIT("東京,100/大阪", ",", "/", , , "")

こうすると#N/Aの代わりに空文字列が入ります。0や”-“を指定することもできますよ。

SPLIT関数との違い・使い分け

TEXTSPLIT関数とSPLIT関数はどちらも文字列を分割する関数です。違いを表にまとめました。

比較項目TEXTSPLITSPLIT
分割方向列方向 + 行方向列方向のみ
2次元分割対応（col + row delimiter）非対応
区切り文字の扱い文字列全体を1つの区切りとして扱うデフォルトで1文字ずつ区切りとして扱う
空セルの扱いignore_empty引数で制御（デフォルトFALSE）第4引数で制御（デフォルトTRUE）
大文字小文字match_mode引数で区別しない設定が可能区別する（変更不可）
埋め値の指定pad_with引数で指定可能なし
Excel互換あり（Microsoft 365）なし（スプレッドシート独自）

使い分けの目安はこちらです。

  • 列方向だけの分割: どちらでもOK。シンプルな分割ならSPLIT関数のほうが引数が少なくて楽です
  • 行方向の分割が必要: TEXTSPLIT関数を使いましょう
  • 2次元に展開したい: TEXTSPLIT関数の独壇場です
  • 1文字ずつ区切りにしたい: SPLIT関数の第3引数TRUEが便利です（TEXTSPLITにはこの機能がありません）
  • Excelとの互換性が重要: TEXTSPLIT関数を使うと、Excelでもそのまま動きます

実務で使える活用例

ここからは仕事で使える具体的な例を紹介します。

CSVデータを表に変換する

テキストファイルからコピーしたCSVデータが1つのセルに入っている場合です。行はCHAR(10)（改行）で区切られ、列はカンマで区切られています。

=TEXTSPLIT(A1, ",", CHAR(10))

これだけで複数行・複数列の表データに変換できます。SPLIT関数では行方向の分割ができないので、1行ずつ処理する必要がありました。TEXTSPLIT関数なら一発ですよ。

住所を都道府県・市区町村に分解する

住所データを区切り文字で分割するケースです。「東京都-千代田区-丸の内1-1」のようにハイフンで区切られたデータなら、こう書けます。

=TEXTSPLIT(A2, "-")

ただし住所のハイフンが番地にも含まれる場合は、意図しない分割になることがあります。そのときは最初の区切りだけ取り出す工夫が必要です。

複数回答アンケートを行に展開する

アンケートの複数回答が「趣味: 読書, 映画, 旅行」のように1セルにまとまっている場合です。

=TEXTSPLIT(A2, "", ", ")

第2引数を空文字列にして、第3引数に「, 」（カンマ+スペース）を指定すると、回答が下方向に展開されます。集計やピボットテーブルで扱いやすい形になりますよ。

TEXTJOINとの相互変換（分割と結合）

TEXTSPLIT関数とTEXTJOIN関数は「分割」と「結合」の逆の関係にあります。セットで覚えておくと便利ですよ。

操作関数例
分割TEXTSPLIT「東京,大阪,名古屋」→ 3つのセルに分割
結合TEXTJOIN3つのセルを → 「東京,大阪,名古屋」に結合

実際に相互変換してみましょう。

分割（TEXTSPLIT）:

=TEXTSPLIT("東京,大阪,名古屋", ",")
→ B1:「東京」, C1:「大阪」, D1:「名古屋」

結合（TEXTJOIN）:

=TEXTJOIN(",", TRUE, B1:D1)
→ 「東京,大阪,名古屋」

同じ区切り文字（カンマ）を使えば、元の形に戻せます。データの分割と結合を行き来する場面で活躍しますよ。

なお、スプレッドシート独自のJOIN関数でも結合できます。TEXTJOINのほうが空セル無視の制御ができるため、一般的にはTEXTJOINがおすすめです。

よくあるエラーと対処法

TEXTSPLIT関数で発生しやすいエラーをまとめます。

エラー原因対処法
#VALUE!区切り文字が未指定第2引数（列の区切り）を必ず指定する。行のみ分割する場合は””を入れる
#N/A行列分割で要素数が揃わない第6引数（pad_with）で””や0を指定して埋める
#REF!分割結果が隣のセルのデータを上書きしようとした展開先に十分な空きセルを確保する
分割されない全角・半角の不一致（「,」と「，」など）区切り文字の全角・半角を確認する
大文字小文字で分割されないデフォルトで大文字小文字を区別する第5引数を1にして区別しない設定にする

特に多いのが#N/Aエラーです。行列分割で各行の要素数が異なると自動的に#N/Aが入ります。これは「データがない」という意味なので、pad_with引数で空文字列を指定するのがおすすめですよ。

まとめ

スプレッドシートのTEXTSPLIT関数の使い方を振り返りましょう。

項目内容
読み方テキストスプリット
機能文字列を区切り文字で列・行・両方向に分割
構文=TEXTSPLIT(テキスト, 列の区切り, [行の区切り], [空を無視], [大小区別], [埋め値])
列方向の分割第2引数に区切り文字を指定
行方向の分割第2引数を””、第3引数に区切り文字を指定
両方向の分割第2引数と第3引数の両方に区切り文字を指定
対になる関数TEXTJOIN（結合）
SPLIT関数との違い行方向・2次元分割に対応。区切り文字は文字列全体で判定

列方向だけの分割ならSPLIT関数でも十分ですが、行方向への分割やCSVデータの2次元展開はTEXTSPLIT関数の出番です。TEXTJOIN関数との相互変換もセットで覚えておくと、文字列操作の幅がグッと広がりますよ。

文字列を途中から取り出したいときはMID関数、余分なスペースを整理したいときはTRIM関数も合わせてチェックしてみてください。

スポンサーリンク
タイトルとURLをコピーしました