【Excel新関数】「@の前」「都道府県だけ」をLEFT／RIGHT／MIDなしで一発切り出し「TEXTBEFORE」「TEXTAFTER」：Tech TIPS
Microsoft Excelでのデータ処理において、文字列の切り出しは頻出する操作である。しかし、従来のLEFT関数やFIND関数などを組み合わせた数式は複雑怪奇になりがちだった。本Tech TIPSでは、そんな文字列操作の苦労から解放してくれる新関数「TEXTBEFORE」と「TEXTAFTER」の使い方を分かりやすく解説する。
この記事は会員限定です。会員登録（無料）すると全てご覧いただけます。
対象：Excel 2024／365
「TEXTBEFORE」「TEXTAFTER」で文字列抽出がここまで簡単になるMicrosoft Excelでのデータ処理において、文字列の切り出しは頻出する操作である。しかし、従来のLEFT関数やFIND関数などを組み合わせた数式は複雑怪奇になりがちだった。本Tech TIPSでは、そんな文字列操作の苦労から解放してくれる新関数「TEXTBEFORE」と「TEXTAFTER」の使い方を分かりやすく解説する。画面の氏名は、「テストデータ・ジェネレータ」で生成したテスト用のもの。
「Microsoft Excel（エクセル）」でデータを処理する際、「文字列を切り出す」必要性に迫られることも多いのではないだろうか。これまで文字列の切り出しといえば、LEFT、RIGHT、MID、FIND、LENといった関数を使うのが一般的だった。ただ、これらの関数を組み合わせた数式は複雑怪奇になりがちで、思ったように動作せず苦労することも多かった。
この「面倒な文字列切り出し手法」から解放してくれるのが、新関数の「TEXTBEFORE」と「TEXTAFTER」の2つである。本Tech TIPSでは、従来の方法と新関数を対比して、新関数を使うことで文字列の切り出しがいかに簡単になるのかを解説する。さらに、要望の多い「住所から都道府県を取り除くスマートな方法」についても紹介しよう。
TEXTBEFORE／TEXTAFTER関数とは
TEXTBEFORE／TEXTAFTER関数は、指定した「区切り文字」を基準にして、それより前（TEXTBEFORE）または後（TEXTAFTER）を抽出する関数だ。
■区切り文字より前を抽出
=TEXTBEFORE(テキスト, 区切り文字)
■区切り文字より後を抽出
=TEXTAFTER(テキスト, 区切り文字)
例えば、「姓 名」というように姓と名が半角スペースで区切られたデータが「A2」セルにあった場合、以下の数式で姓と名を別々のセルに分けることができる。
■半角スペースより前の姓を抽出
=TEXTBEFORE(A2, " ")
■半角スペースより後ろの名を抽出
=TEXTAFTER(A2, " ")
TEXTBEFORE／TEXTAFTER関数で姓と名を分ける半角スペースで区切られた「姓 名」というデータは、半角スペースを区切り文字としたTEXTBEFORE／TEXTAFTER関数で切り出すことができる。
これまでの文字列操作のように、「切り出したい文字が何文字目にあるか」をLEN関数などを使って計算しなくてよいのが大きなメリットだ。
このように便利なTEXTBEFORE／TEXTAFTER関数だが、全てのExcelで使えるわけではない点には注意が必要だ。使用できるのは、Excel 365（Microsoft 365 サブスクリプション版）とExcel 2024（永続ライセンス版）、Excel for the Web（Webブラウザ版）に限定される。Excel 2021／2019／2016以前の永続ライセンス版では使用できない。
非対応の環境でファイルを開くと「#NAME?」エラーになってしまうため、不特定多数の（特に古いExcelを使っている可能性がある）社外メンバーと共有するファイルに組み込む場合は、従来のLEFT関数などを使う方法を検討した方が安全である。
TEXTSPLIT関数とTEXTBEFORE／TEXTAFTER関数の違い
TEXTBEFORE／TEXTAFTER関数と同時に追加された新関数に「TEXTSPLIT」がある。TEXTSPLIT関数は、Tech TIPS「【Excel新関数】『区切り位置指定』はもう古い？ 『TEXTSPLIT』で複雑な文字列分割を数式1つで完結させる」で解説している通り、指定した区切り文字（デリミタ）に基づいて、文字列を複数のセルに展開（スピル）させる関数である。「区切り位置指定」ウィザードを関数にしたようなものといえるだろう。
これらは非常によく似ている。ただ、TEXTBEFORE／TEXTAFTER関数が必要な「一部だけ」を抜き出すのに対し、TEXTSPLIT関数は区切り文字で「全ての要素に分解」して、隣のセルへ展開（スピル）させる、という違いがある。特定の文字の前後だけを切り出したいのであればTEXTBEFORE／TEXTAFTER関数を、特定の文字で切り分けたいのであればTEXTSPLIT関数を使うとよい。
また、REGEXTEST関数で正規表現を使って合致する文字列を抽出するという方法もある（Tech TIPS「【Excel新関数】難問『表記ゆれ』を秒で解決。正規表現関数の基本と実践レシピ」参照のこと）。規則性がありながらも区切り文字などではうまく取り出せないような複雑なケースでは、正規表現によって柔軟に文字列を選んで抽出できるREGEXTEST関数の方が適している場合もある。
具体的な活用例による数式比較
具体的なサンプルデータを用いて、従来のやり方とTEXTBEFORE／TEXTAFTER関数を使った場合の数式を比較してみよう。
メールアドレスから「ドメイン」を取り出す
顧客リストにあるメールアドレスをアカウント名とドメイン名に切り分けて集計や分析などに使いたいという状況は多いだろう。例えば、「yamada@example.com」の「@」より後ろの「example.com」だけを抽出したいといった場合だ。
以前であれば、FIND関数を使い「@」の位置を特定し、全体の文字数（LEN）から引いて右側から何文字切り出すかを計算していた。この数式を見て、何を取り出しているのか瞬時に理解するのは難しいだろう。
=RIGHT(A2, LEN(A2) - FIND("@", A2))
一方、TEXTAFTER関数を使えば、以下の数式でドメイン名部分が切り出せる。これであれば、「A2」セルの文字列から「@」より後ろを切り出していると、直感的に理解できるはずだ。
=TEXTAFTER(A2, "@")
ドメイン名を切り出す（1）FIND関数を使い「@」の位置を特定し、全体の文字数（LEN）から引いて右側から何文字を切り出すのか計算、RIGHT関数で右側から切り出している。画面のメールアドレスは、「テストデータ・ジェネレータ」で生成したダミーデータ。
氏名から「姓」を切り出す
前述のように「姓 名」というデータから「姓」を切り出したい場合、前述のようにTEXTBEFORE関数を使って、区切り文字を半角スペースに指定すればよい。しかし、入力者によって「半角スペース」「全角スペース」「半角スペース2つ（連続）」などが混在している場合、うまく切り出せないことがある。
これまでの方法でスペースの表記ゆれに対応しようとすると、SUBSTITUTE関数を何重にもネストして「全角スペースを半角スペースに変換し、さらに2連続の半角スペースを1つに縮めてからFIND関数で位置を探す」といった複雑な工夫が求められる。さらに忘れやすいのが、FIND関数で見つけた半角スペースの文字数から「-1」した値をLEFT関数で切り出す必要がある点だ。
=LEFT(A3, FIND(" ", SUBSTITUTE(SUBSTITUTE(A3, " ", " "), " ", " ")) - 1)
しかしTEXTBEFORE関数を使えば、第2引数を「{" "," "}」というように配列で指定するだけで済む。配列で各スペースを指定することで、半角と全角のどちらのスペースであっても、「最初に見つかったスペース」を基準に、その手前を切り出してくれる。半角スペースが2つ連続していても、1つ目の半角スペースを検知した時点で処理されるため、余計なエラー対策も不要だ。
=TEXTBEFORE(A2, {" "," "})
氏名から「姓」を切り出す（1）これまでの方法でスペースの表記ゆれに対応しようとすると、SUBSTITUTE関数を何重にもネストして「全角スペースを半角スペースに変換し、さらに2連続の半角スペースを1つに縮めてからFIND関数で位置を探す」といった複雑な工夫が求められる。
氏名から「姓」を切り出す（2）TEXTBEFORE関数なら、第2引数を {" "," "} と配列で指定するだけで済む。配列で指定することで、半角と全角のどちらのスペースであっても、「最初に見つかったスペース」を基準に、その手前を切り出してくれる。
ただし、同じ手法でTEXTAFTER関数を使って「名」を切り出す場合には、少し注意が必要である。半角スペースが2つ連続している場合、最初の半角スペースを基準に切り出されてしまうため、切り出した名の前に半角スペースが1つ残ってしまう。これを防ぐには第2引数を {" "," "," "} というように、2連続の半角スペースを配列の先頭側に含めて指定しておくとよい（全角スペースが2つ連続するケースが含まれる場合も同様）。
=TEXTAFTER(A2, {" "," "," "})
氏名から「名」を切り出す際の注意（1）TEXTAFTER関数で「名」を切り出す際には注意が必要だ。半角スペースが2つ連続している場合、最初の半角スペースを基準に切り出されてしまうため、切り出した名の前に半角スペースが1つ残ってしまう。
住所から「市区町村以降」を切り出す
住所データの中から最初の都道府県名を取り除いた「市区町村以降」を抽出して、都道府県名と市区町村以降を別の列に分けたいということも比較的よくある処理だ。
日本の都道府県名は「3文字（東京都など）」と「4文字（神奈川県など）」が混在するため、従来はMID関数の中にIF関数を仕込んで「4文字目が『県』なら5文字目から切り出す、そうでなければ4文字目から切り出す」という泥臭い条件分岐で処理するケースが一般的だった（Tech TIPS「構造化Excelテク：小難しい関数を使わず、住所を都道府県とそれ以下に分離する方法」参照のこと）。
＝MID(A2, IF(MID(A2,4,1)="県", 5, 4), LEN(A2))
しかし、TEXTAFTER関数を使えば、第2引数に「{"京都府","府","県","道","都"}」というように配列で指定するだけで、どれにヒットしても自動でその1文字後ろから切り出してくれる。
ここでポイントとなるのが「京都府」の扱いだ。単純に {"府","県","道","都"} とすると、「京都府」の「都」に応答してしまい「府京都市……」という切り出しをしてしまう。そのため、最初に「京都府」でヒットするように配列に加えておく必要がある。
=TEXTAFTER(A2, {"京都府","府","県","道","都"})
住所から「市区町村以降」を切り出す（1）MID関数の中にIF関数を仕込んで「4文字目が『県』なら5文字目から切り出す、そうでなければ4文字目から切り出す」ことで、「市区町村以降」を切り出す。ただしこの場合、都道府県が入力されていないと市区町村名から4文字目が切り出されてしまう。画面の住所は、「テストデータ・ジェネレータ」で生成したダミーデータ。
住所から「市区町村以降」を切り出す（2）TEXTAFTER関数を使えば、第2引数に「{"京都府","府","県","道","都"}」というように配列で指定するだけで、どれにヒットしても自動でその1文字後ろから切り出してくれる。都道府県が入力されていないと「#N/A」エラーとなり、元の住所が正しくないことが分かる。
ちなみに「市区町村以降」を抽出した文字列（「C2」セル）を使い、「B2」セルに以下の数式を入力すれば、都道府県が抽出できる。
=TEXTBEFORE(A2, C2)
これは「元の住所（『A2』セル）から、『C2』セルに入っている文字列より前の部分を抜き出す」という指定である。文字数を数えることなく、「C2」セルで切り出した結果を利用して「都道府県名だけ」を芋づる式に抽出することが可能となる。
なお、住所から都道府県が省略されていて、かつ市区町村以降の地名に「都」「道」「府」「県」のいずれかが混じっていると、「#N/A」エラーにならず、誤った位置で区切られてしまう。例えば、「別府市石垣西……」の場合、都道府県は「別府」、市区町村以降は「市石垣西……」となってしまう。厳密さが必要なら、あらかじめ住所を補完して都道府県が必ず含まれるようにした方がよい。
これまでの文字列操作は「左から何文字目」「全体で何文字」という計算が必要になり、数式が複雑になりがちだった。しかし、TEXTBEFORE／TEXTAFTER関数は区切り文字を基準に、その前と後というように分かりやすい切り出し方が可能になっている。特殊なケースを除き、ネスト（関数の重ね掛け）が不要になり、数式が示す処理が見通しやすいというメリットがある。
また、第2引数に「｛｝（配列）」で複数文字が指定できることから、表記にゆらぎのある場合も1つの数式で対応可能だ。
ただ前述の通り、古いExcelのバージョンでは使えない点には注意してほしい。
Copyright© Digital Advantage Corp. All Rights Reserved.
アイティメディアからのお知らせ
注目のテーマ
ITmediaはアイティメディア株式会社の登録商標です。