今回は、ExcelのTEXTBEFORE関数とTEXTAFTER関数を使って、区切り文字の前後にある文字列を抽出する方法を紹介します。メールアドレスのユーザー名とドメイン、ハイフンでつながった商品コード、ファイル名と拡張子など、決まった記号で区切られたデータを分けたい場面はよくあります。従来はLEFT関数とFIND関数を組み合わせるのが定番でしたが、この2つの関数を使えば、数式が短く読みやすくなります。
本記事は、Microsoft 365のExcel（Windows版・Mac版・Web版）とExcel 2024を対象にしています。Excel 2021以前の買い切り版にはこの2つの関数がないため、ファイルを共有する相手の環境にも注意してください。
TEXTBEFORE関数とTEXTAFTER関数の基本
2つの関数の役割
2つの関数は、対になる働きをします。
- TEXTBEFORE関数：指定した区切り文字より前にある文字列を返す
- TEXTAFTER関数：指定した区切り文字より後にある文字列を返す
構文と引数
構文は次のとおりで、引数の並びは2つの関数で共通です。
- =TEXTBEFORE(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
- =TEXTAFTER(text, delimiter, [instance_num], [match_mode], [match_end], [if_not_found])
- text（必須）：対象の文字列やセル。
- delimiter（必須）：区切りの目印になる文字または文字列。
- instance_num（省略可）：何番目の区切り文字を基準にするか。既定は1で、マイナスの数を指定すると末尾側から数えます。
- match_mode（省略可）：0で大文字と小文字を区別（既定）、1で区別しません。
- match_end（省略可）：1にすると、文字列の末尾を区切り文字とみなします。既定は0です。
- if_not_found（省略可）：区切り文字が見つからないときに返す値。省略すると#N/Aエラーになります。
最初の2つだけでも十分に使えます。残りの引数は、必要になったときに足していく使い方で問題ありません。
基本の使い方：区切り文字の前後を取り出す
メールアドレスをユーザー名とドメインに分ける
セルA2に「yamada@example.co.jp」と入力されているとします。
- =TEXTBEFORE(A2,”@”) → yamada
- =TEXTAFTER(A2,”@”) → example.co.jp
同じことをLEFT関数とFIND関数で書くと「=LEFT(A2,FIND(“@”,A2)-1)」のようになり、文字数の計算を意識する必要があります。TEXTBEFORE関数なら「どの記号の前か」を指定するだけなので、数式の意図が読み取りやすくなります。
氏名を姓と名に分ける
「山田 太郎」のように、姓と名の間にスペースが入っている場合は、区切り文字にスペースを指定します。
- =TEXTBEFORE(A2,” “) → 山田
- =TEXTAFTER(A2,” “) → 太郎
ここで注意したいのが、全角スペースと半角スペースは別の文字として扱われる点です。データに両方が混ざっていると、片方だけ見つからずにエラーになります。事前にSUBSTITUTE関数や置換機能でどちらかに揃えておくと、結果が安定します。
何番目の区切り文字かを指定する
2つ目の区切り文字より前を取り出す
「東京都-港区-芝公園」のように、同じ区切り文字が複数ある場合は、3つ目の引数instance_numで基準を選びます。
- =TEXTBEFORE(A2,”-“) → 東京都
- =TEXTBEFORE(A2,”-“,2) → 東京都-港区
- =TEXTAFTER(A2,”-“) → 港区-芝公園
マイナスの数で末尾から数える
区切り文字の数がデータごとに異なる場合は、末尾から数える指定が便利です。instance_numに-1を指定すると、最後の区切り文字が基準になります。
- =TEXTAFTER(A2,”-“,-1) → 芝公園（最後の区切り文字より後）
- =TEXTBEFORE(A2,”-“,-1) → 東京都-港区（最後の区切り文字より前）
この方法は、ファイル名から拡張子を取り出すときにも役立ちます。「report_2026.final.xlsx」のようにピリオドが複数含まれていても、=TEXTAFTER(A2,”.”,-1)なら「xlsx」、=TEXTBEFORE(A2,”.”,-1)なら「report_2026.final」を取り出せます。
2つの関数を組み合わせて中央部分を取り出す
「東京都-港区-芝公園」から真ん中の「港区」だけを取り出したいときは、関数を入れ子にします。
- =TEXTBEFORE(TEXTAFTER(A2,”-“),”-“) → 港区
まずTEXTAFTER関数で「港区-芝公園」を取り出し、その結果に対してTEXTBEFORE関数で最初のハイフンより前を取り出す、という流れです。MID関数で開始位置と文字数を計算するより、考え方がシンプルになります。
知っておくと役立つ引数の使い方
大文字と小文字を区別しない（match_mode）
「TEL:03-xxxx」「Tel:03-xxxx」「tel:03-xxxx」のように表記が揺れているデータでは、既定のままだと一部しか一致しません。4つ目の引数に1を指定すると、大文字と小文字を区別せずに検索できます。
- =TEXTAFTER(A2,”tel:”,,1)
途中の引数を省略するときは、このようにカンマだけを続けて書く点に注意してください。
見つからないときの表示を決める（if_not_found）
区切り文字を含まないセルがあると、既定では#N/Aエラーが表示されます。一覧表にエラーが並ぶと見づらいため、6つ目の引数で代わりの表示を指定しておくと安心です。
- =TEXTAFTER(A2,”@”,,,,”未登録”)
IFERROR関数で囲む方法もありますが、if_not_foundを使うと「区切り文字が見つからなかった場合」に絞って対処できます。ほかの原因によるエラーまで隠してしまうことを避けやすい点がメリットです。
末尾を区切り文字として扱う（match_end）
氏名の一覧に、姓と名の区切りがない「ソクラテス」のようなデータが混ざっている場合を考えます。=TEXTBEFORE(A2,” “)では、スペースがないため#N/Aになります。5つ目の引数に1を指定して=TEXTBEFORE(A2,” “,,,1)とすると、文字列の末尾が区切り文字とみなされ、「ソクラテス」がそのまま返ります。区切りがあるデータでは通常どおり前半部分が返るので、混在した一覧をまとめて処理したいときに役立ちます。
実務で使うときのコツと注意点
範囲を指定してまとめて抽出する
Microsoft 365のExcelでは、第1引数にセル範囲を指定すると、結果が下のセルへ自動的に広がります（スピル）。たとえば=TEXTBEFORE(A2:A100,”@”)と入力すれば、数式を1つ入れるだけで全行のユーザー名を取り出せます。スピル先のセルに値が入っていると#SPILL!エラーになるため、結果を表示する範囲は空けておきましょう。
取り出した数字は文字列になる
「商品A-1200」から=TEXTAFTER(A2,”-“)で取り出した「1200」は、文字列として返ります。そのまま合計したり並べ替えたりすると、意図どおりにならない場合があります。数値として使いたいときは、=VALUE(TEXTAFTER(A2,”-“))のようにVALUE関数で変換しておくと扱いやすくなります。
エラーの原因を押さえておく
- #N/A：区切り文字が見つからない、またはinstance_numが区切り文字の出現回数より大きい
- #VALUE!：instance_numに0を指定した、またはinstance_numが文字列の長さを超えている
また、区切り文字にワイルドカードは使えないため、「*」や「?」で曖昧に指定する使い方はできません。
元データを残しておく
関数で抽出した結果は、元のセルが変わると連動して変わります。元データを削除したい場合は、抽出結果をコピーして[値の貼り付け]で値に変換してから削除すると、結果が消えるのを防げます。
まとめ
TEXTBEFORE関数は区切り文字の前、TEXTAFTER関数は区切り文字の後ろを取り出す関数です。基本は「対象」と「区切り文字」の2つを指定するだけで、メールアドレスや氏名、コード番号などを手早く分けられます。
さらに、instance_numで何番目の区切り文字を使うかを選んだり、マイナスの数で末尾から数えたりすると、区切り文字の数がそろっていないデータにも対応できます。見つからないときの表示を決めるif_not_found、表記揺れに強くするmatch_modeも組み合わせれば、区切り文字による抽出作業を数式1つで安定して処理できるようになります。まずは手元の一覧表で、LEFT関数とFIND関数で書いていた数式を置き換えるところから試してみてください。