氏名や住所を列に分ける。Excelとスプレッドシートの関数の違い

分割専用の機能は、製品と版で違う

• 区切りで横に分ける、Excel(Microsoft 365・2024)：TEXTSPLIT 関数、Excel(2021・2019・2016)：データ → テキストを列に変換、Googleスプレッドシート：SPLIT 関数、またはデータ → テキストを列に分割
• 連続する区切り、Excel(Microsoft 365・2024)：既定では空セルができる、Excel(2021・2019・2016)：ウィザードで設定を確認、Googleスプレッドシート：既定では1つの区切りとして扱う
• 半角・全角の両方で分ける、Excel(Microsoft 365・2024)：区切りを {" ","　"} と並べる、Excel(2021・2019・2016)：先に全角を半角へ置換、Googleスプレッドシート：区切りに " 　" と書くと各文字が区切りになる
• 区切りがない文字列、Excel(Microsoft 365・2024)：LEFT・MIDなどで位置を指定、Excel(2021・2019・2016)：同左、Googleスプレッドシート：同左

Excel:TEXTSPLIT

TEXTSPLIT関数のページの構文は TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) です。ignore_empty の既定は FALSE、pad_with の既定は #N/A です。複数の区切りを使うときは配列定数を使います。対象は Microsoft 365 と Excel 2024 などで、2021以前は載っていません。

pad_with は、1つの結果の中で行ごとに列数が違うときに足りない部分を埋める引数です。行ごとに別々の式を入れた場合に、行数の差を自動で補う機能ではありません。

=TEXTSPLIT(A2,{" ","　"},,TRUE)

半角と全角のスペースを区切りにし、連続した区切りの間の空セルを作りません。

2021以前では 区切り位置指定ウィザード を使います。データタブの「テキストを列に変換」から「区切り記号付き」を選び、プレビューを見ながら進めます。

Googleスプレッドシート:SPLIT

SPLIT関数のヘルプの構文は SPLIT(テキスト, 区切り文字, [各文字での分割], [空のテキストを削除]) です。ここがExcelとの大きな違いです。

• 「各文字での分割」の既定は TRUE です。区切り文字に書いた文字列の各文字が、それぞれ区切りになります。FALSE にすると、文字列全体を1つの区切りとして扱います。
• 「空のテキストを削除」の既定は TRUE です。連続した区切りは1つとして扱われ、FALSE なら間に空のセルが入ります。
• 区切り文字は結果に含まれず、同じ行の別のセルに展開されます。

=SPLIT(A2," 　")

半角と全角のスペースを両方とも区切りにする式です。ヘルプの説明から組んだもので、実行して確かめてはいません。

画面操作は テキストの分割に関するヘルプ にあります。対象のセルを選び、データ → テキストを列に分割と進みます。区切り文字は横のプルダウンで変えられ、自動検出も選べます。

つまり同じ「スペースで分ける」でも、Excelは連続した区切りの扱いを自分で指定します。スプレッドシートは既定で詰めてくれます。ブックとシートを行き来する人は、ここで結果がずれやすくなります。

共通の関数で分ける

専用関数を使わなければ、版の違いを気にせず済みます。A列=氏名、B列=住所として、作業列を使います。

C2: =TRIM(SUBSTITUTE(A2,"　"," "))
D2: =LEFT(C2,SEARCH(" ",C2&" ")-1)
E2: =TRIM(MID(C2,SEARCH(" ",C2&" ")+1,99))

C2は全角スペースを半角に置き換え、TRIMで余分な空白を詰めます。全角はSUBSTITUTEで先に置き換えるので、TRIMが全角をどう扱うかに頼りません。MicrosoftのTRIM関数のページによると、ExcelのTRIMが削除するのは半角スペース(値32)です。Webからコピーした文字に混じる改行しないスペース(値160)は、単独では削除されません。

C2&" " と末尾にスペースを足すと、「鈴木太郎」のように区切りのない行でもSEARCHがエラーになりません。D列に全体が入り、E列は空欄になります。式の組み立て自体はExcelとスプレッドシートで同じですが、スプレッドシート側の結果は未確認です。全角スペースを含む最初の10行ほどで見比べてください。

住所は「都道府県」までなら式で切れる

都道府県名は、神奈川県・和歌山県・鹿児島県が4文字で、残りは3文字です(編集部で数えました)。

F2: =IF(OR(LEFT(B2,4)="神奈川県",LEFT(B2,4)="和歌山県",LEFT(B2,4)="鹿児島県"),LEFT(B2,4),LEFT(B2,3))
G2: =MID(B2,LEN(F2)+1,200)

G2は都道府県より後ろの住所です。「〒」や郵便番号から始まる行、先頭にスペースがある行では、F2が都道府県になりません。市区町村以降は表記ゆれが大きく、区切り指定だけで安定して切るのは難しい部分です。

うまく分かれない例

次は説明用の架空データです。

• 山田　太郎(全角スペース)、起きやすいこと：半角スペースだけを区切りにすると1列のまま残る
• 佐藤 花子(スペース2個)、起きやすいこと：TEXTSPLITの既定では間に空セルができる。SPLITの既定では詰められる
• 鈴木太郎、起きやすいこと：区切りがなく、区切り指定だけでは姓名の境目を決められない
• 高橋 一郎 様、起きやすいこと：敬称が3列目に入る。共通式ではE列に「一郎 様」と入る
• 東京都千代田区サンプル1-2-3 サンプルビル4F、起きやすいこと：スペースは建物名の前だけで、市区町村の境目ではない
• 大阪府大阪市サンプル区1丁目2番3号、起きやすいこと：区切りがなく、都道府県以降は区切り指定では切れない

分けられない行は、式の結果を待つより「見つける条件」を作るほうが実用的です。AIにはその条件も頼みます。

AIへの相談文

実データではなく、上のような置き換えた例を入れてください。

Excel(Microsoft 365)とGoogleスプレッドシートの両方で使える数式を作りたいです。
TEXTSPLITやSPLITは使わず、LEFT・MID・SEARCH・TRIM・SUBSTITUTEなど
両方にある関数で組んでください。

【シートの構成】
A列:氏名、B列:住所(1行目は見出し、2行目からデータ)
出力先:C列=整えた氏名(作業用)、D列=姓、E列=名、F列=都道府県、G列=それ以降の住所

【データの例(すべて架空です)】
山田　太郎(全角スペース)
佐藤  花子(スペース2個)
鈴木太郎(区切りなし)
高橋 一郎 様(敬称付き)
東京都千代田区サンプル1-2-3 サンプルビル4F
大阪府大阪市サンプル区1丁目2番3号

【お願い】
1. C〜G列に入れる数式を、2行目用に1本ずつ示してください。
2. 上の例それぞれで、結果がどうなる想定かを表にしてください。
3. 正しく分けられない行を判定する式(例:E列が空、または「様」を含む)も作ってください。
4. 元の列は変更せず、別の列に出す方法にしてください。

返ってきた式は、例を自分のシートに貼り、予想表と実際の表示が一致するかを見てから使います。片方の製品だけ動かないときは、製品名とエラー表示(#NAME? など)をそのまま伝えると直しやすくなります。

実データに使う前の確認

実データの氏名・住所をAIに貼ってよいかは、社内の取り決めで人が判断します。渡すのは形式が分かる数行までにして、中身は架空の文字列に置き換えてください。

作業前に元のシートを複製し、仕上げに次の3点を確認します。

1. 分割後の行数が元と同じか
2. E列(名)が空、または敬称・数字を含む行が何件あるか
3. F列(都道府県)に都道府県名以外が入っていないか(47都道府県の一覧と照合し、COUNTIF などで数える)

漏れた行は手作業で直す前提にしておくと、精度が100%でなくても作業が止まりません。姓名の境目がない行は、名簿や申込書などの元の情報で確認します。