【Excel】エクセルで特定の文字を抽出する方法（関数・別シート）

エクセルの一覧表から、商品名の一部、メールアドレスのドメイン、住所に含まれる都道府県名など、必要な文字だけを取り出したい場面は少なくありません。

文字列を目で確認してコピーする方法では、データ量が増えるほど時間がかかり、転記ミスも起こりやすくなります。

LEFT関数、RIGHT関数、MID関数、FIND関数を組み合わせれば、文字の位置や区切り記号を基準にして自動抽出できます。

さらにFILTER関数や参照式を利用すると、条件に合うデータだけを別シートへ一覧化することも可能です。

この記事では、1行目が見出し行であるサンプルデータを使い、エクセルで特定の文字を抽出する関数と別シートへの抽出方法を詳しく解説します。

数式を入力した後にオートフィルでコピーする流れも確認し、日々の集計やリスト作成に役立てましょう。

 

エクセルで特定の文字を抽出する関数の選び方

A列 商品コード	B列 商品名	C列 抽出結果
TK-001-RED	赤色ボールペン	TK
OS-015-BLU	青色ノート	OS

それではまず、文字列のどこを取り出したいかに応じた関数の選び方について解説していきます。

特定の文字を抽出する作業では、対象文字が左端、右端、途中のどこにあるかを先に整理すると、数式を迷わず作成できます。

 

左端の文字を取り出すLEFT関数

LEFT関数は、セルに入力された文字列の左側から指定した文字数だけを抜き出す関数です。

商品コードの先頭にある分類記号や、氏名の先頭にある部署コードなど、開始位置が常に左端で固定されているデータに向いています。

たとえばA2セルのTK-001-REDから先頭2文字のTKを抽出する場合は、C2セルに次の数式を入力します。

LEFT関数の最初のA2は抽出元となるセル、2は左から取り出す文字数です。

この例では先頭2文字が分類コードであるため、文字数を2に指定します。

入力後にC2セルを選択し、右下に表示される小さな四角であるフィルハンドルを下方向へドラッグすると、各行に対応した数式をコピーできます。

セル参照はA2からA3、A4へ自動的に変わるため、行ごとに数式を書き直す必要はありません。

 

右端の文字を取り出すRIGHT関数

RIGHT関数は、文字列の右側から必要な文字数を取得する関数です。

末尾に色コード、支店番号、拡張子、年度などが付いているデータで活用できます。

たとえばA2セルのTK-001-REDから、末尾3文字のREDだけを取り出す場合は、次のように入力します。

数式の3は右端から数える文字数を表します。

文字数が必ず同じである末尾コードなら、RIGHT関数だけで短く分かりやすい数式になります。

末尾の文字数が商品ごとに異なる場合は、RIGHT関数だけでは対応しにくいため、後述するFIND関数やTEXTAFTER関数を検討しましょう。

英数字だけでなく、日本語の文字も通常は1文字として数えられます。

 

途中の文字を取り出すMID関数

MID関数は、文字列の途中から指定した文字数を取り出す関数です。

商品コードTK-001-REDの中央にある001のように、開始位置と文字数があらかじめ決まっているデータに適しています。

A2セルからハイフンを除いた中央3文字を取得する場合は、次の数式です。

最初のA2は対象セル、4は左から数えた開始位置、3は抽出する文字数です。

TK-001-REDでは、Tが1文字目、Kが2文字目、最初のハイフンが3文字目になるため、4文字目から抽出します。

文字列の構造が統一されているなら、MID関数は途中の番号や区分を安定して取り出せる方法です。

 

区切り文字を基準にした文字列の抽出

A列 メールアドレス	B列 ユーザー名	C列 ドメイン
sato@example.co.jp	sato	example.co.jp
tanaka@sample.jp	tanaka	sample.jp

続いては、ハイフンやアットマークなどの区切り文字を手掛かりにして抽出する方法を確認していきます。

区切り位置が行によって変わるデータでは、固定位置を指定するMID関数よりも、FIND関数などを組み合わせる方法が実用的です。

 

FIND関数で記号の位置を調べる方法

FIND関数は、指定した文字や記号が文字列の何文字目にあるかを返す関数です。

メールアドレスのアットマーク、氏名の空白、商品コードのハイフンなど、抽出の境目を探すときに使います。

A2セルのsato@example.co.jpでアットマークの位置を調べる数式は次のとおりです。

結果は5となり、アットマークが5文字目にあることが分かります。

FIND関数は大文字と小文字を区別するため、英字の検索では検索条件を正確に指定することが大切です。

大文字と小文字を区別せずに探したい場合には、FIND関数ではなくSEARCH関数を使用します。

 

アットマークより前を取り出す数式

メールアドレスからユーザー名だけを取り出す場合は、LEFT関数とFIND関数を組み合わせます。

B2セルへ入力する数式は次の形です。

FIND(“@”,A2)はアットマークの位置を返します。

そこから1を引くことで、アットマークの直前までの文字数になり、LEFT関数がsatoを抽出します。

この考え方は、姓と名の間に入った空白、住所内の区切り記号、型番の最初のハイフンより前を取得する場合にも応用できます。

記号そのものを結果に含めたくないときは、位置から1を引く処理を忘れないようにしましょう。

 

アットマークより後を取り出す数式

メールアドレスからドメイン部分だけを取り出す場合は、MID関数、FIND関数、LEN関数を組み合わせます。

C2セルには次の数式を入力します。

FIND(“@”,A2)+1によって、アットマークの次の文字を開始位置に指定します。

LEN(A2)はA2セル全体の文字数を返すため、十分に大きな文字数として末尾まで抽出できます。

Microsoft 365やExcel 2021以降を利用している場合は、より短い数式としてTEXTAFTER関数も利用できます。

TEXTAFTER関数は指定した区切り文字の後ろを直接取り出せるため、新しいエクセル環境では特に読みやすい方法です。

 

条件に合うデータを別シートへ抽出する方法

A列 商品名	B列 区分	C列 在庫数
赤色ボールペン	文具	120
青色ノート	文具	85
白色マグカップ	雑貨	42

続いては、指定した文字を含む行や条件に一致するデータを別シートへ抽出する方法を確認していきます。

元データを加工せずに抽出結果を表示できるため、検索用シートや報告用シートを作成する場合に便利です。

 

FILTER関数で文字を含む行を一覧化する方法

FILTER関数は、条件に一致した複数行のデータを別の場所へまとめて表示する関数です。

元データが入力されたシート名を一覧、抽出先のシート名を抽出結果とした場合を考えます。

抽出結果シートのA2セルへ、商品名に赤色を含む行を表示する数式を入力します。

SEARCH関数は、一覧シートの商品名に赤色という文字がある位置を返します。

ISNUMBER関数は、その結果が数値であるかを判定し、文字が見つかった行をTRUEとしてFILTER関数へ渡します。

FILTER関数では、条件に合う件数が増減しても結果範囲が自動的に広がるため、行数を事前に確保する必要がありません。

この画面では、抽出結果シートのA2セルに数式を入力し、スピル機能によってA列からC列へ結果を展開しています。

 

別シート参照で単一セルを表示する方法

別シートにある特定セルの文字をそのまま表示したいだけなら、単純なシート参照を利用します。

たとえば一覧シートのA2セルを抽出結果シートのB2セルに表示する数式は次のとおりです。

シート名に空白が含まれる場合は、シート名をシングルクォーテーションで囲みます。

商品一覧というシート名なら、=’商品一覧’!A2という形です。

別シート参照は元データを更新すると抽出先も連動して変わるため、二重入力を防げます。

見出し行を含めずにデータだけを参照するなら、元シートの2行目から数式を設定することが基本です。

 

抽出条件をセルで変更する方法

検索したい文字を数式内へ直接入力するのではなく、抽出結果シートのE1セルなどに入力しておくと、利用者が条件を自由に変更できます。

E1セルに検索語を入力し、A2セルに次の数式を設定します。

この数式では、E1セルの内容が赤色なら赤色を含む商品だけが表示され、文具なら文具という文字を含む商品名が表示されます。

検索語を空白にしたときの扱いも考慮したい場合は、IF関数を組み合わせて空白時には一覧全体を表示する設定にするとよいでしょう。

条件入力セルを用意すると、数式を編集できない人でも検索条件だけを切り替えられます。

 

複数条件と部分一致による文字列の抽出

A列 商品名	B列 区分	C列 在庫数
赤色ボールペン	文具	120
赤色マグカップ	雑貨	15

続いては、複数の条件を指定したり、文字の一部だけを条件にしたりする抽出方法を確認していきます。

実務では色名だけでなく、区分や在庫数もあわせて絞り込むケースが多いため、条件式の作り方を理解しておきましょう。

 

複数条件をすべて満たす行の抽出

商品名に赤色を含み、さらに区分が文具である行だけを抽出したい場合は、FILTER関数の条件を掛け算でつなぎます。

エクセルではTRUEを1、FALSEを0として扱えるため、掛け算はAND条件として機能します。

両方の条件がTRUEである行だけが1になり、FILTER関数の抽出対象になります。

複数条件のAND抽出では、条件式を丸括弧で囲むと数式の構造を確認しやすくなります。

 

どちらかの条件を満たす行の抽出

赤色または青色を含む商品を抽出したい場合は、条件式を足し算でつなぎます。

足し算はOR条件として働き、いずれか一方がTRUEなら抽出対象になります。

同じ商品名に赤色と青色の両方が含まれていた場合でも、FILTER関数の結果にはその行が1回だけ表示されます。

条件語が増えると数式は長くなりますが、検索語を別セルに配置して管理すると見通しがよくなります。

 

ワイルドカードを使った部分一致検索

COUNTIF関数やXLOOKUP関数では、アスタリスクをワイルドカードとして使い、任意の文字列を表せます。

ただし本文中では記号の入力方法を目的にせず、部分一致という考え方を理解することが重要です。

FILTER関数で部分一致を行うなら、SEARCH関数を用いる方法が分かりやすく、検索語が文字列の先頭、中間、末尾のどこにあっても対応できます。

SEARCH関数は文字列が見つかった位置を返すため、ISNUMBER関数と組み合わせることで部分一致の判定式になります。

 

抽出できないときのエラー確認

A列 元データ	B列 数式	C列 確認項目
田中 太郎	=LEFT(A2,FIND(” “,A2)-1)	空白の種類
該当データなし	=FILTER(…)	条件範囲

続いては、数式を入力しても特定の文字を抽出できないときの確認項目を解説していきます。

エラーの原因は、文字そのものではなく、空白、記号、参照範囲、データ形式にあることもあります。

 

FIND関数で発生するエラーの原因

FIND関数で検索した文字が対象セルに存在しない場合は、数値エラーが表示されます。

氏名の間に半角空白があると想定していても、実際には全角空白が入力されていることがあります。

見た目が似ていても別の文字として扱われるため、検索対象の空白やハイフンをコピーして数式へ貼り付ける方法が確実です。

文字列に区切り記号が存在しない行が混在する場合は、IFERROR関数でエラー表示を整えると一覧が見やすくなります。

 

FILTER関数のスピルエラー確認

FILTER関数の結果が広がる予定のセル範囲に文字や数式が入っていると、スピルエラーが表示されます。

抽出先のA2セルから右側、下側に必要な空白範囲があるかを確認しましょう。

結果範囲に結合セルが含まれている場合も、配列結果を展開できない原因になります。

抽出専用シートでは、FILTER関数の出力範囲をできるだけ空白のまま確保しておくことが安全です。

 

参照範囲と見出し行の確認

サンプルデータでは1行目に見出しがあるため、FILTER関数やSEARCH関数の参照範囲はA2から始めます。

見出し行まで条件範囲に含めると、検索語と見出し文字が偶然一致した場合に不要な行が抽出される可能性があります。

抽出する配列の行数と、条件に使う範囲の行数は必ず同じにする必要があります。

FILTER関数の配列範囲と条件範囲の開始行、終了行をそろえることが、エラーを防ぐ基本です。

 

まとめ エクセルで特定の文字を抽出する方法

目的	主な関数	活用例
左側の文字	LEFT	先頭の分類コード
途中の文字	MID、FIND	記号間の番号
別シートへの抽出	FILTER、SEARCH	条件に合う一覧

エクセルで特定の文字を抽出する方法では、文字列の位置が固定されているか、区切り記号の位置が変わるかを見極めることが出発点です。

先頭から抜き出すならLEFT関数、末尾ならRIGHT関数、途中ならMID関数を使います。

区切り文字の前後を抽出する場合は、FIND関数で位置を調べてからLEFT関数やMID関数へ組み合わせます。

条件に合う複数行を別シートへ表示したい場合は、FILTER関数とSEARCH関数の組み合わせが効率的です。

1行目を見出しとして除外し、2行目から同じ数式をオートフィルでコピーすれば、大量のデータでも一貫した処理ができます。

抽出結果がエラーになるときは、全角と半角の違い、検索文字の有無、参照範囲の行数、スピル先の空白を確認しましょう。

関数による文字抽出を身につけると、手作業のコピーを減らし、検索、集計、別シートへの転記をより正確に進められます。