Excelで氏名や住所、商品名などを入力したセルを、スペースの位置で複数のセルへ分けたい場面は少なくありません。
従来は区切り位置機能を使う方法が定番でしたが、Microsoft 365やExcel 2021以降では、TEXTSPLIT関数を使うことで元データを残したまま分割結果を表示できます。
TEXTSPLIT関数は、半角スペースや全角スペースを区切り文字として指定し、数式だけで横方向または縦方向にデータを分割できる関数です。
この記事で分かること
・TEXTSPLIT関数でスペース区切りの文字列を分割する方法
・半角スペースと全角スペースを同時に扱う数式
・連続スペース、空白セル、エラーへの対処法
ここでは、1行目に見出しがある表を例に、スペースを使ってセルを安全に分ける手順を詳しく解説します。
TEXTSPLIT関数で半角スペースを使ってセルを分ける方法
それではまず、TEXTSPLIT関数で半角スペース区切りの文字列を横方向へ分ける方法について解説していきます。
| A列 | B列 | C列 |
|---|---|---|
| 氏名 | 姓 | 名 |
| 山田 太郎 | 山田 | 太郎 |
| 佐藤 花子 | 佐藤 | 花子 |
数式を入力するセルの選択
分割した結果を表示したい先頭セルを選択します。
今回の例では、A列に氏名があり、B列へ姓、C列へ名を表示するため、B2セルを選択します。
TEXTSPLIT関数は、結果を複数セルへ自動展開するスピル機能を使います。
そのため、B2セルの右側にあるC2セルなどには、あらかじめ文字や数式を入力しないようにしましょう。
スピル先にデータが入っていると、数式は入力できても結果が表示されず、スピルエラーになることがあります。
入力先は、分割後の左上にしたいセルです。
氏名を姓と名へ横並びで分ける場合は、B2セルを起点にする考え方になります。
半角スペースを指定するTEXTSPLIT関数
B2セルに次の数式を入力します。
=TEXTSPLIT(A2,” “)
1つ目のA2は、分けたい文字列が入っているセルです。
2つ目の半角スペースを囲んだ部分は、文字列を切り分けるための列区切り記号です。
数式を確定すると、A2セルの山田 太郎が、B2セルの山田とC2セルの太郎に自動で分かれます。
半角スペースで区切られたデータなら、TEXTSPLIT関数だけでコピーや置換をせずに分割できます。
1行目がヘッダーの場合は、B1セルに姓、C1セルに名と入力しておくと、表の意味が分かりやすくなります。
オートフィルとスピル範囲の確認
B2セルに数式を入力したあと、セル右下のフィルハンドルを下へドラッグすると、各行に対応した数式をコピーできます。
ただし、Excelのバージョンや表の状態によっては、数式を入力したセルの結果が横方向に広がる点を確認する必要があります。
A3セルが佐藤 花子であれば、B3セルには佐藤、C3セルには花子が表示されます。
結果が想定と異なる場合は、元の文字列に半角スペース以外の空白が含まれていないか確認しましょう。
【操作のポイント】TEXTSPLIT関数の結果が広がる先には、空白のセルを確保します。
全角スペースと半角スペースを同時に分ける数式
続いては、全角スペースと半角スペースが混在したデータを分ける方法を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 氏名 | 姓 | 名 |
| 鈴木 一郎 | 鈴木 | 一郎 |
| 高橋 美咲 | 高橋 | 美咲 |
全角スペースだけを区切り文字にする設定
データが全角スペースだけで区切られている場合は、区切り文字として全角スペースを入力します。
=TEXTSPLIT(A2,” ”)
数式内の引用符の間には、キーボードで全角スペースを1文字入れます。
見た目では半角スペースとの違いが分かりにくいため、コピーしたデータを扱うときは特に注意が必要です。
全角スペースと半角スペースはExcelでは別の文字として扱われるため、区切り文字は元データに合わせる必要があります。
SUBSTITUTE関数で空白の種類を統一する方法
全角と半角が混在するデータでは、先に全角スペースを半角スペースへ置き換える方法が実用的です。
=TEXTSPLIT(SUBSTITUTE(A2,” ”,” “),” “)
SUBSTITUTE関数は、A2セルにある全角スペースを半角スペースへ変換します。
その変換後の文字列をTEXTSPLIT関数へ渡すため、空白の種類が混在していても同じ規則で分割できます。
空白の表記ゆれをSUBSTITUTE関数で整えてからTEXTSPLIT関数へ渡すと、名簿や住所録の処理が安定します。
全角スペースは数式内で判別しづらいため、入力時には元データから対象の空白をコピーする方法も便利です。
複数の区切り文字を配列で指定する方法
TEXTSPLIT関数では、区切り文字を配列として指定できます。
=TEXTSPLIT(A2,{” “,” ”})
この数式では、半角スペースと全角スペースのどちらがあっても分割対象になります。
データの入力ルールが統一されていない場合には、SUBSTITUTE関数を重ねるより短い数式で済むことがあります。
ただし、データに意図しない空白が含まれると、想定外の位置で分割される可能性もあります。
【操作のポイント】空白が混在する一覧では、最初に数件のデータで分割結果を確認してから数式を展開します。
TEXTSPLIT関数で縦方向へ分ける設定
続いては、スペースで区切った値を縦方向のセルへ並べる設定を確認していきます。
| A列 | B列 |
|---|---|
| キーワード | 分割結果 |
| Excel TEXTSPLIT スペース | Excel |
| TEXTSPLIT | |
| スペース |
行区切り引数へスペースを指定する数式
TEXTSPLIT関数で縦方向に分けるときは、2番目の列区切り引数を空欄にして、3番目の行区切り引数へスペースを指定します。
=TEXTSPLIT(A2,,” “)
この数式では、A2セル内のExcel TEXTSPLIT スペースが、B2セルから下方向へ1語ずつ表示されます。
列区切りには横方向の分割文字を、行区切りには縦方向の分割文字を指定する仕組みです。
横方向と縦方向を使い分ける考え方
氏名を姓と名に分けるようなデータでは、横方向への分割が扱いやすいでしょう。
一方で、検索キーワードを単語ごとに抽出したい場合や、1つのセルに含まれるタグを一覧化したい場合は、縦方向への分割が便利です。
縦方向に展開された結果は、FILTER関数やUNIQUE関数、COUNTIF関数などとも組み合わせやすい特徴があります。
分割後のデータを集計する予定がある場合は、1項目を1行に置く縦方向の配置が適することがあります。
列区切りと行区切りを同時に使う場面
TEXTSPLIT関数は、列区切りと行区切りを同時に指定することもできます。
たとえば、改行で複数行になった文字列の各行をスペースで分けたいときに活用できます。
=TEXTSPLIT(A2,” “,CHAR(10))
CHAR(10)は、セル内の改行を表す文字です。
スペースで横方向、改行で縦方向に分割されるため、コピー元が複雑な一覧でも表形式へ近づけられます。
【操作のポイント】縦方向に展開する数式では、下側のセルにも十分な空白範囲を確保します。
連続スペースとエラーを防ぐTEXTSPLIT関数の設定
続いては、連続スペースや空白セルによって分割結果が乱れる場合の設定を確認していきます。
| A列 | B列 | C列 |
|---|---|---|
| 氏名 | 姓 | 名 |
| 田中 恒一 | 田中 | 恒一 |
ignore_empty引数で空の要素を無視する設定
名前の間に半角スペースが2つ以上連続している場合、通常のTEXTSPLIT関数では空白の要素が含まれることがあります。
このような場合は、4番目のignore_empty引数にTRUEを指定します。
=TEXTSPLIT(A2,””,TRUE)
ただし、上の数式は区切り文字の指定を省略しているため、スペースで分ける場合は次の形で入力します。
=TEXTSPLIT(A2,” “,,TRUE)
TRUEを指定すると、連続した区切り文字の間にある空の結果を無視できます。
TRIM関数で余分な空白を整理する方法
TEXTSPLIT関数の前にTRIM関数を使うと、文字列の先頭や末尾にある余分な半角スペースを整理できます。
=TEXTSPLIT(TRIM(A2),” “)
TRIM関数は、複数連続する半角スペースを1つへまとめる働きもあります。
ただし、全角スペースはTRIM関数だけでは整理できないため、全角スペースがあるデータではSUBSTITUTE関数との併用が必要です。
インポートしたCSVやWebからコピーした文字列では、まず余分な空白を整えると後工程のエラーを減らせます。
スピルエラーと未対応バージョンへの対処
数式を入力しても結果が表示されず、スピルと表示される場合は、分割先のセルに値が残っている可能性があります。
数式セルを選択し、警告アイコンから妨げになっているセルを確認します。
また、TEXTSPLIT関数は動的配列数式に対応したExcelで利用できる関数です。
古いExcelで関数名が認識されない場合は、区切り位置機能やLEFT関数、MID関数、FIND関数を使う方法を検討しましょう。
【操作のポイント】エラー表示が出たら、スピル範囲の空きとExcelの対応バージョンを順に確認します。
TEXTSPLIT関数と区切り位置機能の使い分け
続いては、TEXTSPLIT関数と区切り位置機能をどのように使い分けるか確認していきます。
| 方法 | 向く作業 |
|---|---|
| TEXTSPLIT関数 | 元データを残して自動更新したい場合 |
| 区切り位置機能 | 一度だけデータを確定して分割したい場合 |
元データと連動させたい場合
TEXTSPLIT関数の大きな利点は、元のセルを変更すると分割結果も自動で更新されることです。
毎月更新される名簿や、別シートから参照する商品データなどでは、数式による分割が役立ちます。
元データを残したまま加工結果を表示したいなら、TEXTSPLIT関数が適しています。
分割結果を値として確定したい場合
取引先へ渡すファイルなどで、数式を含めずにデータを確定したい場合もあります。
その場合は、TEXTSPLIT関数で分割した範囲をコピーし、値として貼り付ける方法が使えます。
または、データタブの区切り位置機能でスペース区切りを選び、直接セルを分ける方法もあります。
ただし区切り位置機能は、分割先に既存データがあると上書きする可能性があるため注意しましょう。
関数を組み合わせるデータ整形
TEXTSPLIT関数は、TEXTJOIN関数やFILTER関数、UNIQUE関数と組み合わせることで、より柔軟なデータ整形ができます。
たとえば、スペース区切りで分けた単語から重複を除き、別のセルへ一覧として表示することも可能です。
分割は単独の作業ではなく、検索、抽出、集計のための前処理として活用できます。
【操作のポイント】更新のたびに再分割したい表は関数、一度だけ確定したい表は区切り位置機能を選びます。
まとめ TEXTSPLIT関数でスペースを使ってセルを分ける方法
Excelでスペースを使ってセルを分けるには、TEXTSPLIT関数を使う方法が便利です。
半角スペースで横方向に分ける基本の数式は、=TEXTSPLIT(A2,” “)です。
全角スペースが混じる場合は、SUBSTITUTE関数で半角スペースへ統一するか、複数の区切り文字を指定しましょう。
縦方向へ分けたいときは、=TEXTSPLIT(A2,,” “)のように行区切り引数へスペースを指定します。
TEXTSPLIT関数を使えば、元データを残しながら、スペース区切りの文字列を見やすい表へ自動で分割できます。
連続スペースがあるデータではTRIM関数やignore_empty引数を活用し、スピル範囲にデータがないことも確認しましょう。
目的に応じてTEXTSPLIT関数と区切り位置機能を使い分けることで、Excelのデータ整理を効率化できます。
