メインコンテンツへスキップ
見出し画像

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に登録して、「分割」 とひとこと送ってください。ダウンロードリンクを自動でお返しします。

    画像

    https://lin.ee/UqFmzfB

     
     
    \初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

    あなたへのおすすめ