【Excel】TEXTBEFORE・TEXTAFTER関数で特定の文字より前・後ろを抽出する｜文字列の抽出と応用例

従来、特定の文字より前や後ろの文字列を抽出するには、LEFT・MID関数とFIND・SEARCH関数などを組み合わせる方法が一般的でした。

Excel 2024では、TEXTBEFORE関数やTEXTAFTER関数が使用できるようになり、従来よりも簡潔な数式で文字列を抽出できます。例えば、メールアドレスの「@」より前のユーザー名や、後ろのドメイン名も簡単に取り出せます。

本記事では、両関数の基本的な使い方から、2番目以降の区切り記号の指定、大文字・小文字を区別しない抽出方法、エラーの対処法まで、具体例を交えて解説します。

TEXTBEFORE・TEXTAFTER関数の基本的な使い方

TEXTBEFORE関数は、特定の文字より前の文字を抽出します。
TEXTAFTER関数は、特定の文字より後ろの文字を抽出します。

両関数は引数が共通しており、指定した文字が見つからない場合は、通常「#N/A」エラーになります。

TEXTBEFORE(テキスト, 区切り記号, [インスタンス番号], [一致モード], [末尾一致], [一致しなかった場合])
TEXTAFTER(テキスト, 区切り記号, [インスタンス番号], [一致モード], [末尾一致], [一致しなかった場合])

TEXTBEFORE関数・TEXTAFTER関数の引数一覧

引数	省略時	説明
テキスト	不可	検索対象のテキストです。ワイルドカードは使用できません。 テキストが空の文字列の場合、空の文字列を返します。
区切り記号	不可	文字列を抽出する際の基準となる"特定の文字"を指定します。 TEXTBEFORE関数は指定した文字より前、TEXTAFTER関数は後ろを抽出します。
インスタンス番号	1	何番目の区切り記号を基準にするかを指定します。正の数はテキストの先頭から、負の数はテキストの最後から数えます。
一致モード	0	大文字と小文字を区別して検索するかを指定します。 「0」は区別しますが、「1」は区別しません。
末尾一致	0	文字列の末尾（インスタンス番号が負の場合は先頭）を区切り記号として扱うかを指定します。「0」は扱わず、「1」は扱います。
一致しなかった場合	#N/A	指定した区切り記号が見つからなかった場合に返す値を指定します。

TEXTBEFORE・TEXTAFTER関数で文字列を抽出する

TEXTBEFORE関数とTEXTAFTER関数は、第1引数に抽出元の［テキスト］、第2引数に抽出する位置の基準となる［区切り記号］を指定すると、文字列を抽出できます。

=TEXTBEFORE("東京-品川","-")なら、"東京-品川" にある "-" の前の文字「東京」を抽出します。=TEXTAFTER("東京-品川","-")なら、"-" の後ろの「品川」を抽出します。
区切り記号が複数ある場合、第3引数（インスタンス番号）を省略した時は先頭から1番目の区切り記号を基準に抽出します。

=TEXTBEFORE("東京-品川",".")はテキスト（"東京-品川"）の中に区切り記号（"."）がないため、「#N/A」となります。

TEXTBEFORE・TEXTAFTER関数の抽出サンプル表

テキスト	区切り記号	TEXTBEFORE関数	TEXTAFTER関数
sample@example.com	@	sample	example.com
山田　太郎	(全角スペース)	山田	太郎
evidence.xlsx	.	evidence	xlsx
ab.cd.ef	.	ab	cd.ef
名古屋-京都-新大阪	-	名古屋	京都-新大阪
名古屋-京都-新大阪	@	#N/A	#N/A

※山田　太郎（氏名の例）は、区切り記号に全角スペースを指定しています。

2番目以降の［区切り記号］を基準に文字列を抽出する

TEXTBEFORE関数やTEXTAFTER関数に［インスタンス番号］（第3引数）を入力すると、2番目以降の区切り記号を基準に文字列を抽出できます。

例えば、「東京-品川-新横浜」の2番目の「-」を基準に抽出する場合、次のようになります。

• =TEXTBEFORE("東京-品川-新横浜","-", 2)→ 東京-品川
• =TEXTAFTER("東京-品川-新横浜","-", 2)→ 新横浜

［インスタンス番号］に負の数を指定すると、末尾（右側）から区切り記号を数えます。

• =TEXTBEFORE("東京-品川-新横浜","-", -2)→ 東京
• =TEXTAFTER("東京-品川-新横浜","-", -2)→ 品川-新横浜

指定した番号の区切り記号が存在しない場合は、通常「#N/A」エラーになります。
また、［インスタンス番号］にテキストの文字数より絶対値が大きい数値を指定すると、「#VALUE!」エラーになります。

TEXTBEFORE・TEXTAFTER関数の抽出サンプル表

テキスト	区切り記号	インスタンス番号	TEXTBEFORE関数	TEXTAFTER関数
ab.cd.ef	.	2	ab.cd	ef
名古屋-京都-新大阪	-	2	名古屋-京都	新大阪
ab.cd.ef	.	-2	ab	cd.ef
名古屋-京都-新大阪	-	-2	名古屋	京都-新大阪

［区切り記号］の大文字・小文字を区別しない

TEXTBEFORE関数とTEXTAFTER関数は、通常、大文字と小文字を区別して区切り記号を検索します。
［一致モード］（第4引数）に「1」を指定すると、大文字と小文字を区別せずに検索できます。

省略した場合は「0」として扱われるため、次のようになります。

• =TEXTBEFORE("abcABC","B")→ abcA
• =TEXTAFTER("abcABC","B")→ C

これに対して、［一致モード］に「1」を指定すると大文字小文字を区別しないため、以下のようになります。
※第3引数を指定しない場合は、「,」を続けて入力します

• =TEXTBEFORE("abcABC","B",,1)→ a
• =TEXTAFTER("abcABC","B",,1)→ cABC

"B"と"b"を区別しないため、先に見つかる2文字目の"b"が基準となります。

なお、［一致モード］に「0」や「1」以外を指定すると「#VALUE!」となります。

TEXTBEFORE・TEXTAFTER関数の抽出サンプル表

テキスト	区切り記号	一致モード	TEXTBEFORE関数	TEXTAFTER関数
abcdef	C	0	#N/A	#N/A
abcdef	C	1	ab	def
abcABCabc	A	0	abc	BCabc
abcABCabc	A	1	空文字列（""）	bcABCabc

末尾を区切り記号として扱う

［末尾一致］（第5引数）に「1」を指定すると、文字列の端を区切り記号として扱います。
インスタンス番号が正の数の場合、文字列の末尾を、負の数の場合は文字列の先頭を区切り記号として扱います。

そのため、指定した区切り記号が存在しない場合や、区切り記号の数がインスタンス番号より1つ少ない場合でも、エラーにならずに抽出できることがあります。

一方、指定した区切り記号が十分に存在する場合は、［末尾一致］を「1」にしても抽出結果は変わりません。

TEXTBEFORE・TEXTAFTER関数の抽出サンプル表

テキスト	区切り記号	インスタンス番号	末尾一致	TEXTBEFORE関数	TEXTAFTER関数
abcdef	b	1	0	a	cdef
abcdef	b	1	1	a	cdef
abcdef	b	2	0	#N/A	#N/A
abcdef	b	2	1	abcdef	空文字列（""）
abcdef	b	-2	0	#N/A	#N/A
abcdef	b	-2	1	空文字列（""）	abcdef

［区切り記号］が見つからない場合のメッセージを変更する

TEXTBEFORE関数やTEXTAFTER関数は、指定した［区切り記号］が見つからなかった場合、通常「#N/A」エラーを返します。
［一致しなかった場合］（第6引数）に文字列やセルを指定すると、区切り記号が見つからなかった際に、指定した値を返すことができます。

例えば、次のように指定します。
※ 第3～第5引数を省略して第6引数を指定するため、区切り記号の後にはカンマ（,）を4つ続けて入力します。

• =TEXTBEFORE(A1,".",,,,".が見つかりません")
  → セルA1に「.」がない場合、「.が見つかりません」と返します
• =TEXTBEFORE(A1,".",,,,A1)
  → セルA1に「.」がない場合、セルA1の値をそのまま返します

【応用】複数の［区切り記号］を指定する

TEXTBEFORE関数・TEXTAFTER関数は、第2引数に配列定数を指定すると、複数の［区切り記号］を検索できます。
※配列定数は {} で囲み、複数の値を半角カンマで区切ります。

半角の「-」と全角の「－」を検索して、区切り記号にしたい場合は、次のように指定します。

=TEXTBEFORE(A1, {"-","－"})
=TEXTAFTER(A1, {"-","－"})

これにより、半角・全角のどちらのハイフンが使われていても、［区切り記号］として認識できます。

複数の［区切り記号］が［テキスト］に存在する場合は、指定したインスタンス番号に対応する［区切り記号］を基準に抽出します。

複数の区切り記号が存在する場合、配列定数に記述した順番ではなく、テキスト内で出現する順番に数えます。
例えば、A-B－C では、半角の「-」が1番目、全角の「－」が2番目の区切り記号になります。

TEXTBEFORE・TEXTAFTER関数の抽出サンプル表

テキスト	区切り記号	インスタンス番号	TEXTBEFORE関数	TEXTAFTER関数
A-B	{"-","－"}	1	A	B
A－B	{"-","－"}	1	A	B
A-B－C	{"-","－"}	1	A	B－C
A－B-C	{"-","－"}	1	A	B-C
A-B－C	{"-","－"}	2	A-B	C

TEXTBEFORE・TEXTAFTER関数と従来の関数を比較する

Excel 2021以前では複数の関数を組み合わせる必要があった文字列の抽出も、Excel 2024ではTEXTBEFORE・TEXTAFTER関数を使って、より簡潔な数式で実現できます

Excel2024以降	Excel2021まで
=TEXTBEFORE(A1,"-")	=LEFT(A1,FIND("-",A1)-1)
=TEXTBEFORE(A1,".",2)	=LEFT(A1,SEARCH(".",A1,SEARCH(".",A1)+1)-1)
=TEXTAFTER(A1,".")	=RIGHT(A1,LEN(A1)-FIND(".",A1))
=TEXTAFTER(A1,"abc",,1)	=MID(A1,SEARCH("abc",A1)+3,LEN(A1))

「#N/A」エラーや「#VALUE!」エラーの原因と対処法

#N/Aエラーについて

TEXTBEFORE関数やTEXTAFTER関数で「#N/A」エラーが表示される場合は、次の点を確認してください。

• 区切り記号が存在するか
  テキストに区切り記号が含まれているか、再確認してください。
• 区切り記号が似た記号になっていないか
  「－」と「─」など、見た目が似た文字でないか確認してください。
• インスタンス番号が大きすぎないか
  インスタンス番号を指定している場合、この数が区切り記号の個数より多いと「#N/A」となります。
  例えば、区切り記号が2個しかないのに、インスタンス番号に「3」を指定した場合です。
• 全角/半角が違っていないか
  半角の「ABC」に対して全角の「Ｂ」を指定しても一致しません。
  必要に応じてASC関数やJIS関数で、区切り記号の表記を統一してください。
• セルの表示形式により、データの見た目が変わっていないか
  セルに「2027/1/1」と表示されていても、実際には日付のシリアル値「46388」が保存されている場合があります。
  この場合、区切り記号「/」を指定しても、シリアル値には「/」が含まれていないため、通常「#N/A」エラーになります。
  日付の一部を抽出したい場合は、TEXT関数で日付を文字列に変換してから抽出してください。
  =TEXTBEFORE(TEXT(A1,"yyyy/m/d"),"/")
  セルA1の表示形式が［日付］で「2027/1/1」と表示されているなら、上記数式で「2027」を抽出できます。

#VALUE!エラーについて

第6引数の［一致しなかった場合］を設定していても、ほかの引数に問題があると「#VALUE!」エラーになることがあります。「#VALUE!」エラーが表示された場合は、次の点を確認してください。

• インスタンス番号
  ［インスタンス番号］には「0」以外の数値を指定してください。
  また、数値の絶対値がテキストの文字数を超えないようにしてください。
• 一致モード
  「0」もしくは「1」を指定してください。
• 末尾一致
  「0」もしくは「1」を指定してください。

エラーは出ないが、意図した文字列を抽出できない場合

エラーが表示されなくても、想定した位置の文字列を抽出できない場合があります。
次の点を確認してください。

• 区切り記号の位置とインスタンス番号
  インスタンス番号の正負を間違えると、基準となる区切り記号の位置が変わります。
  また、区切り記号が複数存在する場合、第3引数を省略すると、先頭から1番目の区切り記号が基準になります。
• 省略した引数とカンマの数
  TEXTBEFORE関数・TEXTAFTER関数には6つの引数があります。
  第3～第5引数を省略して後ろの引数を指定する場合は、引数の位置を空けるためにカンマ（,）を続けて入力します。
  カンマの数や位置を間違えると、指定した値が別の引数として扱われ、意図しない結果になる場合があります。

まとめ

TEXTBEFORE関数は指定した文字より前、TEXTAFTER関数は指定した文字より後ろの文字列を抽出する関数です。

インスタンス番号を指定すれば、2番目以降の区切り記号や、末尾から数えた区切り記号を基準に抽出することもできます。また、大文字・小文字を区別しない検索や、複数の区切り記号の指定にも対応しています。

従来はLEFT・MID・FIND関数などを組み合わせていた処理も、TEXTBEFORE・TEXTAFTER関数なら簡潔な数式で実現できます。文字列から必要な部分だけを取り出したい場合に、ぜひ活用してみてください。

参考資料

• Microsoft「TEXTBEFORE 関数」
  https://support.microsoft.com/ja-jp/excel/functions/textbefore-function
• Microsoft「TEXTAFTER 関数」
  https://support.microsoft.com/ja-jp/excel/functions/textafter-function