
1つのセルに全部詰まった名簿を、関数だけで一瞬でバラした話
人からもらうExcelの名簿は、なぜかいつも「1つのセルに全部入っている」。
氏名のセルに「山田 太郎」。住所のセルに「東京都渋谷区神南1-2-3」。
ひどいときは連絡先のセルに「090-1111-2222,yamada@example.com」と、電話番号とメールが1つのマスにカンマで押し込まれている。
これを、姓と名に分けたい。都道府県だけ抜きたい。電話とメールを別々の列にしたい。——やること自体はよくある話だ。問題は、その「分ける」がびっくりするほど面倒くさいことにある。
昔の私は、これを関数で頑張ろうとしていた。姓を取り出すなら、スペースの位置をFINDで探して、そこまでをLEFTで切る。名はMIDで残りを取る。書いてみるとこうなる。
=LEFT(A2, FIND(" ", A2) - 1)
一見それっぽい。実際、きれいに「姓 名」と入っている行では、ちゃんと動く。
でも、名簿は現実なので、きれいな行ばかりじゃない。
「鈴木一郎」——スペースが無い。するとFINDが「スペースが見つからない」で#VALUE!を吐いて、数式が丸ごと折れる。
「高橋 みなみ」——ここのスペースが全角だった。半角スペースを探しているFINDはこれを見つけられず、姓のセルに「高橋 みなみ」がそのまま丸ごと入る。
分けたつもりが、分かれていない。
つまり、LEFTとMIDとFINDを組み合わせた数式は、入力が1文字でも想定とズレると静かに壊れる。しかも壊れ方が2種類ある。
エラーになって目立つならまだいい。厄介なのは、エラーも出さずに「間違った結果」を平然と返してくるパターンだ。
ぱっと見は分かれているように見えるから、気づかずにそのまま使ってしまう。

このやり方の何が本質的にダメだったかというと、「区切りの位置」を人間が計算させられていることだった。何文字目にスペースがあるか。そこから何文字取るか。
全部こちらが指示しないといけない。だから区切りの位置が動いた瞬間に破綻する。
やりたいことは、もっと単純なはずだ。「スペースで割って」「カンマで割って」——場所じゃなくて、区切り文字を指定したいだけ。
それを、そのまま書ける関数が、実はもうExcelに入っている。
TEXTSPLIT。名前のとおり、テキストを割る関数だ。「この文字で割って」と区切り文字を渡すだけ。
何文字目とか、FINDとか、LEFTとか、一切いらない。
仲間にTEXTBEFORE(区切りの前だけ取る)、TEXTAFTER(区切りの後だけ取る)、そして逆にバラバラの列を1本につなげるTEXTJOINがいる。
この4つを覚えた日から、私の「1セルに詰まった問題」は、体感で消えた。
氏名の列を丸ごと選んで数式ひとつ。全員ぶんの姓と名が、下にずらっとこぼれ落ちる。1つ1つコピーする必要も、数式を下までドラッグする必要もない。
カンマで詰まった連絡先も、1発で電話とメールに割れる。
極めつけは、CSVをうっかり1セルに貼り付けてしまった、あの絶望的な状態すら、たった1つの数式で表に戻せる。
この記事では、その4つの関数を、実際の名簿を題材に最初から最後まで使ってみせる。
姓名の分割、住所や連絡先の切り出し、崩れたCSVの復元、そして逆向きの「宛名づくり」まで。全部、画面そのまま載せる。
コピペして自分の列名に変えれば動く形にしてある。
もしあなたのExcelに、いまだにLEFTとMIDとFINDで組んだ長い数式が残っているなら、今日それを1行に短くできる。
⚠️ 1. まず「対応バージョン」を正直に。ここだけ先に確認してほしい
いい話から入りたいところだけど、先に制約を言っておく。技術的な制約を隠して後で「動きません」となるのが一番よくない。
この記事の主役、TEXTSPLIT / TEXTBEFORE / TEXTAFTER は、Microsoft 365 と、買い切りなら Excel 2024 以降でしか使えない。
Excel 2021 や 2019 以前で打つと、#NAME?(そんな関数は知らない、の意味)が返ってくる。
自分のExcelが対応しているかは、空いているセルにこう打てば1秒で分かる。
=TEXTBEFORE("a-b", "-")
これで a と返ればOK。#NAME? なら、残念だけどこの記事の前半は使えない。
ただ、逆向きの連結に使う TEXTJOIN は Excel 2019 でも使える。
だから「分割は無理でも、連結(第6章)はできる」という人もいるはず。そこは章ごとに分けて書くので、自分のバージョンで使えるところだけ持って帰ってほしい。
前置きは以上。ここから作っていく。
📋 2. 土台になる「名簿」を用意する(触るのはここだけ)
題材にするのは、どこにでもある顧客名簿だ。列はこうなっている。
氏名:「山田 太郎」のように、姓と名が半角スペースで入っている
郵便番号・住所・建物名:宛名に使う住所パーツ(建物名は空の人もいる)
連絡先:「電話番号,メールアドレス」が、カンマ区切りで1セルに詰まっている
この「名簿」シートだけが、人が手で触る場所だ。ここに自分のデータを貼れば、後で作る分割・連結のシートは全部自動で追従する。

逆に言えば、分割結果のセルは絶対に手で書き換えない。あそこは数式が計算する「結果の画面」であって、入力欄ではない。
大事な下ごしらえが1つある。区切り文字を統一しておくことだ。氏名を半角スペースで割るつもりなら、名簿の氏名は全部半角スペースで揃える。
ここに全角スペースが1つでも混ざると、その行だけ割れない。
目では半角と全角の区別がつかないので、これは最初に潰しておくのが楽だ(後述の第7章で、混ざったときの直し方も書く)。
土台ができた。ここから、この名簿を材料に4つの関数を回していく。
✂️ 3. 姓と名に分ける ── 数式ひとつで、全員ぶん
一番よくある「姓名を分けたい」から。
冒頭で見たLEFT+FINDの地獄を、TEXTBEFOREとTEXTAFTERはこう片付ける。
姓 → =TEXTBEFORE(名簿!A3, " ")
名 → =TEXTAFTER(名簿!A3, " ")
読み下すと、TEXTBEFORE(文字, " ") は「スペースより前を取れ」、TEXTAFTER(文字, " ") は「スペースより後を取れ」。
それだけ。何文字目かを数える作業が、まるごと消えている。区切り文字を渡すだけだから、名前の長さが人によって違っても関係ない。
そして、ここがこの世代の関数の真骨頂なんだけど——セル1つじゃなく、列を丸ごと渡せる。
=TEXTBEFORE(名簿!A3:A8, " ")
A3ではなくA3:A8と範囲で渡すと、結果が下にずらっとこぼれ落ちる(スピル、という)。全員ぶんの姓が、この数式ひとつで一気に出る。
数式を下までドラッグする必要はない。1つ書いて、Enter。それで名簿の人数ぶん、勝手に並ぶ。名簿に人が増えれば、この列も勝手に伸びる。
「1セルを、その場で2列に割りたい」ならTEXTSPLITの出番だ。
=TEXTSPLIT(名簿!A3, " ")
これは「山田 太郎」を、右方向に「山田」「太郎」と2つのセルに割る。
TEXTBEFORE/TEXTAFTERが縦に取り出すのに対し、TEXTSPLITは横に割る、と覚えるといい。
姓名みたいに「1個の区切りで2つに割る」場面はこれが速い。
実際に名簿を流し込むと、こうなる。左が元の氏名、そこから緑のセルに姓と名がこぼれている。TEXTSPLITで2列に割った結果も並べてある。

(※TEXTSPLITに列の範囲をまるごと渡すと、行ごとの2列展開はうまく揃わない。
全員ぶんを縦に出したいときは、上のTEXTBEFORE/TEXTAFTERを列で渡すのが確実。
ここは実際に試して確かめた挙動なので、素直にそう使うのがいい。)
📞 4. 「電話,メール」の1セルを、3つに割る
次は、あの憎いやつ。連絡先のセルに「090-1111-2222,yamada@example.com」とカンマで押し込まれているパターン。
区切りがカンマになっただけで、やることは第3章と同じだ。
電話 → =TEXTBEFORE(名簿!E3:E8, ",")
メール → =TEXTAFTER(名簿!E3:E8, ",")
カンマの前が電話、後ろがメール。これも範囲で渡しているから、全員ぶんが一気に出る。
もう一歩踏み込むと、メールアドレスからドメインだけを抜くのもワンパターンでできる。メールの中の@で後ろを取ればいい。
しかも、さっきTEXTAFTERが吐いたメールの結果を、そのまま材料にできる。
ドメイン → =TEXTAFTER(C4#, "@")
C4#の#は「その数式がこぼした範囲、まるごと」を指す書き方(スピル参照)。
メール列が下に何行こぼれていても、C4#と書けば全部を受けてドメインを取ってくれる。
example.comとsample.jpが、それぞれの行にきれいに並ぶ。

TEXTBEFOREとTEXTAFTERは、この「区切りの前だけ/後だけ欲しい」に無限に効く。
ファイル名から拡張子、URLからドメイン、伝票番号から枝番——身の回りの「1セルに2つ入っている」は、だいたいこの2つで割れる。
🧩 5. 1セルに貼れてしまったCSVを、1つの数式で表に戻す
これが個人的に一番「おおっ」となったやつ。
CSVをコピーして、Excelのセルにそのまま貼り付けてしまったことはないだろうか。本来なら列に分かれてほしいのに、なぜか全部が1つのセルに、改行込みでドサッと入る、あの状態。
日付,商品,数量,金額
7/1,デスクA,4,96000
7/2,チェアB,6,84000
7/3,棚C,3,45000
これが丸ごとセルA4に入っている。普通なら「区切り位置」機能を使うところだけど、TEXTSPLITはこれを数式ひとつで表に戻せる。TEXTSPLITには区切りを2つ渡せるからだ。
=TEXTSPLIT(A4, ",", CHAR(10))
1つ目の","が列の区切り、2つ目のCHAR(10)が行の区切り。
CHAR(10)はセル内改行を表す文字コードで、「改行のところで行を変えろ」という意味になる。
これで、カンマと改行で詰まった1セルが、縦4行×横4列のちゃんとした表として、右下にこぼれ落ちる。
実際にやってみると、上の詰まった1セルから、下にきれいな表が復元される。日付・商品・数量・金額が、それぞれの列にきちんと収まっている。

「区切り位置」ウィザードを何回もクリックしていたのが馬鹿らしくなる。
🔗 6. 逆方向 ── バラバラの列を、1本の宛名につなげる(TEXTJOIN)
ここまでは「割る」話だった。今度は逆、つなげる話。
宛名ラベルを作りたい、みたいな場面だ。郵便番号・住所・建物名・氏名がバラバラの列にある。これを「〒150-0041 東京都渋谷区神南1-2-3 山田 太郎 様」と1本につなげたい。
&で全部つなげてもいいんだけど、それだと問題が起きる。建物名が空の人がいるからだ。空セルをそのまま&で挟むと、その部分が余計な空白になって「住所 氏名」みたいに間延びする。
これを賢く片付けるのがTEXTJOINだ。
=TEXTJOIN(" ", TRUE, "〒"&名簿!B3, 名簿!C3, 名簿!D3, 名簿!A3&" 様")
引数を前から読むと、" "が「間に挟む文字(ここでは半角スペース)」、そして2番目のTRUEが効く。これは「空欄は飛ばせ」という指示だ。
だから建物名が空の人は、その空白ごと自動でスキップされて、詰まった自然な宛名になる。建物名がある人は、ちゃんとそこに挟まる。
実際に流すと、建物名の無い山田さんは「〒150-0041 東京都渋谷区神南1-2-3 山田 太郎 様」と余分な空白なし、建物名のある佐藤さんは「…北1条西2 ノース札幌ビル5F 佐藤 花子 様」と、ビル名がきちんと入る。

空欄のあるなしを気にせず、同じ数式が全員に効く。これが&べた書きとの決定的な差だ。
TEXTJOINは、複数の商品を1セルにカンマ区切りでまとめる、タグを「/」でつなぐ、といった用途にもそのまま効く。「バラバラを1個にまとめたい」が来たら、まずこれを思い出してほしい。
🔁 7. データが増えても、勝手に追従する ── そして、つまずきポイント
ここまでの分割・連結は、全部名簿を参照している数式でできている。ということは、名簿にデータを足せば、結果も勝手に付いてくる。
試しに名簿の一番下に、中村さんを1行足してみる。

すると、姓名分割のシートは何も触っていないのに、中村さんの「中村」「直樹」が、下に勝手に増える(範囲をA3:A9のように1行ぶん広げておくのがコツ。
最初からA3:A100のように余裕を持たせておけば、足すたびに自動で入る)。1回作れば、あとは名簿に貼るだけ。

これがスピルの気持ちよさで、一度これに慣れると、数式を下までドラッグする作業に戻れなくなる。
最後に、実際に組むと必ずどこかで踏むやつを3つ。
1つ目:全角スペースと半角スペースが混ざっている。 これが一番多い。
TEXTBEFORE(A, " ")と半角スペースを指定しているのに、データ側が全角「 」だと、その行だけ割れずに丸ごと出る。見た目で区別できないのが厄介。
=SUBSTITUTE(A3, " ", " ")で全角スペースを半角に統一してから割ると、事故が消える。区切り文字の全角半角は、最初に揃えるのが鉄則。
2つ目:#SPILL!(スピル!)が出る。 これは「こぼしたい先に、もう何か入っている」というエラー。
TEXTBEFOREを範囲で書いて全員ぶんこぼそうとしたのに、その真下のセルに古いデータが残っていると出る。こぼれる先(右と下)を空けてあげれば直る。
エラーではあるけど「行き先が塞がってますよ」という親切な警告だと思えばいい。
3つ目:途中に空行を作らない。 名簿の途中に空っぽの行があると、その行でTEXTBEFOREが「空の文字には区切りが無い」とつまずく。
データは上から詰めて入れる。減らすときは一番下から消す。これだけ守れば安定する。
📝 8. コピペ用テンプレート(列名を変えれば動く)
自分のシートですぐ組めるように、この記事で使った数式を全部まとめておく。シート名(名簿)と範囲を、自分の表に合わせて変えるだけで動く。
■ 姓を取り出す(全員ぶん・下にこぼれる)
=TEXTBEFORE(名簿!A3:A8, " ")
■ 名を取り出す
=TEXTAFTER(名簿!A3:A8, " ")
■ 1セルを2列に割る(横方向)
=TEXTSPLIT(名簿!A3, " ")
■ 「電話,メール」を割る
電話 =TEXTBEFORE(名簿!E3:E8, ",")
メール =TEXTAFTER(名簿!E3:E8, ",")
■ メールからドメインを取る(スピル参照 #)
=TEXTAFTER(C4#, "@")
■ 1セルに詰まったCSVを表に戻す(列区切り "," /行区切り 改行)
=TEXTSPLIT(A4, ",", CHAR(10))
■ バラバラの列を1本の宛名につなげる(TRUE=空欄は飛ばす)
=TEXTJOIN(" ", TRUE, "〒"&名簿!B3, 名簿!C3, 名簿!D3, 名簿!A3&" 様")
■ 全角スペースを半角に統一してから割る(混在対策)
=TEXTBEFORE(SUBSTITUTE(A3, " ", " "), " ")
最後のSUBSTITUTEとの合わせ技は、実務では本当によく使う。汚いデータをその場で整えてから割れるので、名簿の元データを触らずに済む。
LEFTとMIDとFINDで「何文字目か」を計算させられていた時代は、もう終わっていい。
やりたかったのは、ずっとシンプルなことだった。「スペースで割って」「カンマで割って」「空欄は飛ばしてつなげて」。それを、そのまま書ける関数が来た。
TEXTSPLIT・TEXTBEFORE・TEXTAFTER・TEXTJOIN。この4つだけで、1セルに詰まった名簿は怖くなくなる。
もし今、長いLEFT(…FIND…)の数式がシートに残っているなら、今日それを消して、TEXTBEFOREの1行に置き換えてみてほしい。次に汚い名簿が来たときの気分が、たぶん少し変わる。
🛠️ 自分の表でも同じことをしたいけれど、作る時間が無い。そういう時は、表をそのまま預かって組んでいる。
作ったものは、全部ここにまとめてある。
この記事で作った「文字列 分割・連結テンプレート」を、そのまま使える状態で無料で配っています。
名簿に自分のデータを貼るだけで、姓名の分割・連絡先の切り出し・CSVの復元・宛名づくりが全部自動で動きます(「使い方」シート付き・マクロなし・Excel 2024/365向け)。
📩 下のLINEに登録して、「分割」 とひとこと送ってください。ダウンロードリンクを自動でお返しします。
