ExcelのTEXTSPLIT関数の使い方をお探しですね。

広告

ExcelのTEXTSPLIT関数で文字列をサクッと分割！基本から応用まで

ExcelでCSVデータやWebからコピーした情報を扱っていると、「商品A,商品B,商品C」とか「山田 太郎」みたいに、1つのセルにいろんな情報がギュッと詰まっていることってありますよね。

今までは「区切り位置」機能を使ったり、難しい関数を組み合わせたりして対応していましたが、TEXTSPLIT関数を使えば、カンマやスペースなどの区切り文字を指定するだけで、あっという間に複数のセルへ分けることができます。

この記事では、Excelの便利な新機能・TEXTSPLIT関数について、基本的な使い方から、横や縦への分割方法、複数の区切り文字への対応、実際に使うときに気をつけたいポイントまで、わかりやすく解説していきます。

TEXTSPLIT関数って何？どんなときに使えるの？

TEXTSPLIT関数は、指定した区切り文字を目印にして、1つのセルに入っている文字列を複数のセルに分けてくれる関数です。

例えば、A1セルに「東京,大阪,名古屋」って入力されているとします。

そこで `=TEXTSPLIT(A1,”,”)` と入力すると、「東京」「大阪」「名古屋」が横方向に並んで表示されます。

カンマ区切りのCSVデータや、スペースで区切られた氏名、ハイフンでつながったコードなんかを、数式だけでキレイに整理できるんです。

TEXTSPLIT関数のいいところは、ただ文字を分けるだけじゃないんです。

従来の「区切り位置」機能は、一度実行すると結果が固定されちゃって、元のデータを変更したらもう一度やり直す必要がありました。

でもTEXTSPLIT関数は数式として残るので、元データが変わると分割した結果も自動で更新されます。

毎月更新される売上データとか顧客リスト、アンケート結果なんかを扱うときは、作業時間がグッと短くなって、ミスも減らせます。

基本的な書き方は `=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])` です。

必ず指定しないといけないのは、分割したい文字列（`text`）と、横方向に分けるときの区切り文字（`col_delimiter`）の2つ。

角カッコ [ ] で囲まれている部分は省略できるオプションで、縦方向への分割や、空白の扱い方、大文字小文字の区別、足りないセルに何を表示するかなどを細かく調整できます。

ただし、TEXTSPLIT関数はMicrosoft 365やExcel for the webなど比較的新しいバージョンで使える機能です。

買い切り版のExcelだとバージョンによっては使えないことがあります。

入力して `#NAME?` エラーが出たら、お使いのExcelが対応していない可能性があるので注意してください。

カンマやスペース区切りの文字を横に分ける基本の使い方

TEXTSPLIT関数の一番基本的な使い方は、カンマやスペースなどの区切り文字を指定して、文字列を横方向に分ける方法です。

例えば、A1セルに「りんご,みかん,バナナ」と入力されているとします。

結果を表示したいセルに `=TEXTSPLIT(A1,”,”)` と入力してEnterキーを押すと、数式を入れたセルから右方向に「りんご」「みかん」「バナナ」が自動的に表示されます。

この自動展開は「スピル」という仕組みで、1つの数式が複数のセルに結果を返してくれるExcelの便利機能です。

スペース区切りの氏名を分けたいときも考え方は同じです。

A1セルに「山田 太郎」と入っているなら、`=TEXTSPLIT(A1,” “)` と入力すれば、姓と名を別々のセルに分けられます。

ここで気をつけたいのが、区切り文字として使うスペースにも種類があること。

半角スペースで区切られているなら `” “`、全角スペースで区切られているなら `”　”` を指定する必要があります。

見た目はそっくりですが、Excelでは別の文字として扱われるので、うまく分割されないときは元データのスペースが半角か全角かをチェックしてみてください。

複数の区切り文字が混ざっているデータには、配列を使うと便利です。

例えばA1セルに「東京 大阪,名古屋」みたいに半角スペースとカンマが混在している場合、`=TEXTSPLIT(A1,{” “,”,”})` と入力すると、どちらの文字も区切りとして認識してくれます。

実際の仕事では、手入力されたデータほど区切り方がバラバラだったりします。

カンマ、スペース、スラッシュ、ハイフンなどがごちゃ混ぜになっている場合でも、TEXTSPLIT関数なら複数の条件をまとめて指定できるので、表記ゆれのあるデータ整理にピッタリです。

縦方向への分割と実際に使える応用テクニック

TEXTSPLIT関数は横方向だけじゃなくて、縦方向にも文字を分割できます。

横方向の区切り文字は2番目の引数に指定しますが、縦方向に分けたいときは3番目の引数（`row_delimiter`）を使います。

例えばA1セルに「商品A,商品B,商品C」と入っていて、これを縦一列に並べたい場合は、`=TEXTSPLIT(A1,,”,”)` と入力します。

2番目の引数は空欄のままカンマで飛ばして、3番目の引数に区切り文字のカンマを指定するのがポイントです。

これで商品A、商品B、商品Cが下方向に並びます。

改行で区切られたセルを分割したいときにもTEXTSPLIT関数が活躍します。

セル内改行は目に見えにくいので、関数では `CHAR(10)` という書き方で指定します。

例えばA1セル内に複数行のメモや項目が入っている場合、`=TEXTSPLIT(A1,,CHAR(10))` と入力すると、改行ごとに縦方向へ分割できます。

Webフォームから出力した回答データとか、コピーした箇条書きのテキストを一覧にしたいときに便利です。

ただし、Windowsの環境やデータの作成元によっては改行コードの扱いが違うことがあるので、うまく分割できないときはCLEAN関数やSUBSTITUTE関数で事前に整える方法も検討してみてください。

縦と横の両方を同時に指定すれば、1つのセルにまとまったデータを表の形に展開することもできます。

例えば「A班:田中,佐藤:鈴木,高橋」みたいに、コロンで行を分けて、カンマで列を分けたい場合は、`=TEXTSPLIT(A1,”,”,”:”)` のように指定します。

2番目の引数が列方向、3番目の引数が行方向という順番を覚えておくと、複雑な文字列でも落ち着いて分解できます。

単なる文字列分割じゃなくて、表として再利用できる形に整えられるのが、TEXTSPLIT関数の実務での大きな強みです。

TEXTSPLIT関数で失敗しやすいポイントと他の機能との使い分け

TEXTSPLIT関数を使うときにまず確認したいのが、分割結果を表示するための空きセルがあるかどうかです。

スピル機能では、数式を入力したセルから右方向または下方向に結果が展開されます。

その範囲に既にデータがあると、結果を表示できなくて `#SPILL!` エラーになってしまいます。

この場合は、展開先のセルを空にするか、別の場所に数式を入力してください。

スピル範囲は数式の結果に応じて広がるので、隣接セルに大事なデータを置かないようにしておくと安心です。

次に気をつけたいのが、分割後のデータが文字列として扱われる場合があること。

例えば「100/200」みたいな数値っぽい文字列を `=TEXTSPLIT(A1,”/”)` で分割しても、結果が計算に使える数値として認識されないことがあります。

合計や平均などの計算に使いたいときは、`=VALUE(TEXTSPLIT(A1,”/”))` みたいにVALUE関数を組み合わせると、文字列の数字を数値に変換できます。

余計な記号が混ざっている場合は、SUBSTITUTE関数で取り除いてから分割するか、分割後に置換する方法が効果的です。

例えばカッコを消したいときは、`=SUBSTITUTE(TEXTSPLIT(A1,”,”),”)”,””)` みたいな考え方で整形できます。

省略できる引数も、実務では知っておくと便利です。

4番目の引数（`ignore_empty`）は、区切り文字が連続していて生じる空欄を無視するかどうかを指定します。

例えば「A,,B」みたいにカンマが連続しているデータでは、空の項目を残すのか、詰めて表示するのかを選べます。

5番目の引数（`match_mode`）は大文字と小文字を区別するかを指定して、6番目の引数（`pad_with`）は縦横に展開したとき足りないセルに何を表示するかを指定します。

省略時に `#N/A` が表示される場合は、`pad_with` に空文字 `””` や「該当なし」などを指定すると、見やすい表に整えやすくなります。

TEXTSPLIT関数と「区切り位置」機能、Power Queryは、目的によって使い分けると効率的です。

– **TEXTSPLIT関数**：元データを残したまま、自動更新される分割結果を作りたいときに便利
– **区切り位置**：一度だけサクッと分割したいときに便利だけど、元データの変更には連動しない
– **Power Query**：数千件以上の大量データや、複数ファイルの取り込み、定期的なデータ整形に強い

日常的なリスト整形はTEXTSPLIT関数、単発作業は区切り位置、大規模で繰り返し発生する処理はPower Queryという基準で考えると、無理なく使い分けられます。

まとめ

TEXTSPLIT関数は、カンマやスペース区切りの文字列を複数セルに分割するだけじゃなくて、データ更新にも強い整理用の数式として活用できます。

CSVの分割、氏名の分解、セル内改行の一覧化、不要文字の除去、数値変換まで組み合わせれば、手作業でやっていた前処理の多くを自動化できます。

Excelでデータを扱う機会が多い人は、まず `=TEXTSPLIT(A1,”,”)` の基本形から試してみて、次にスペース、複数区切り文字、縦方向分割へと広げていくと理解しやすいです。

最新のExcel環境を使っているなら、TEXTSPLIT関数はこれからのデータ整理で絶対に覚えておきたい重要な関数です。

ぜひ活用してみてください！

広告