Excelで作業をしていると、大量のデータの中から特定の文字列を探し出したい場面は頻繁に訪れます。
顧客リストから特定の地域名を含む住所を抽出したい、商品コードに特定の文字が含まれているものを見つけたい、メールアドレスのドメインを検索したいなど、膨大なセルの中から目的の文字列を効率的に見つけ出す作業は、ビジネスの現場では欠かせないスキルです。
一つずつ目視で確認していては時間がかかりすぎますし、見落としのリスクも高まります。データ件数が数百、数千となれば、手作業での対応は現実的ではありません。
Excelには文字を検索する方法が複数用意されています。
Ctrl+Fキーで呼び出せる検索ダイアログによる検索、FIND関数やSEARCH関数を使った関数による検索、VLOOKUP関数やXLOOKUP関数を使った高度な検索など、それぞれに特徴があり、状況に応じて最適な方法が異なります。
本記事では、セル内の文字を検索する様々な方法を詳しく解説し、部分一致検索や後ろからの検索、あいまい検索のテクニックや使い分けのポイントを紹介します。
データ分析作業を効率化したい方は、ぜひ最後までお読みください。
ポイントは
・FIND関数とSEARCH関数で文字列の位置を特定できる
・検索ダイアログを使えばワイルドカードで柔軟な検索が可能
・関数を組み合わせることで後ろからの検索や条件付き抽出も実現
です。
それでは詳しく見ていきましょう。
FIND関数とSEARCH関数で文字列の位置を特定する
それではまず、最も基本的な関数を使った文字検索の方法を確認していきます。
FIND関数の基本的な使い方
文字列の中から特定の文字を探し出し、その位置を返すには、FIND関数を使用します。
FIND関数は「=FIND(検索文字列,対象文字列,開始位置)」という構文で、検索文字列が対象文字列の何文字目に最初に現れるかを数値で返します。
例えば、A1セルに「東京都渋谷区」という文字列が入っている場合、B1セルに「=FIND(“区”,A1)」と入力すれば、「区」は6文字目にあるため「6」という数値が返されます。
開始位置を省略した場合は1文字目から検索が開始されます。開始位置を指定すると、その位置以降から検索を始めることができます。「=FIND(“区”,A1,5)」とすれば、5文字目以降から「区」を探します。
FIND関数の動作イメージ
基本形
→ 6
開始位置指定
→ エラー
メール検索
→ @の位置
FIND関数は検索文字列が見つからない場合、エラー値「#VALUE!」を返します。このため、検索文字列の有無を判定したい場合は、ISNUMBER関数やIFERROR関数と組み合わせて使用します。「=ISNUMBER(FIND(“区”,A1))」とすれば、「区」が含まれていればTRUE、含まれていなければFALSEが返されます。
| A列の値 | 数式(B列) | 結果 | 意味 |
|---|---|---|---|
| 東京都渋谷区 | =FIND(“区”,A1) | 6 | 6文字目に「区」がある |
| user@example.com | =FIND(“@”,A1) | 5 | 5文字目に「@」がある |
| 商品コード-A-001 | =FIND(“-“,A1) | 6 | 6文字目に最初の「-」がある |
| 札幌市中央区 | =FIND(“区”,A1) | 6 | 6文字目に「区」がある |
SEARCH関数との違いと使い分け
FIND関数と似た機能を持つ関数として、SEARCH関数があります。
SEARCH関数の構文は「=SEARCH(検索文字列,対象文字列,開始位置)」で、FIND関数と全く同じです。しかし、重要な違いが2つあります。
1つ目の違いは、FIND関数は大文字と小文字を区別しますが、SEARCH関数は区別しません。A1セルに「Excel」という文字列がある場合、「=FIND(“excel”,A1)」はエラーになりますが、「=SEARCH(“excel”,A1)」は「1」を返します。英語のテキストを扱う場合は、この違いが重要になります。
2つ目の違いは、SEARCH関数ではワイルドカード文字が使用できることです。「?」は任意の1文字、「*」は任意の文字列を表します。「=SEARCH(“商品?”,A1)」とすれば、「商品A」「商品1」などにマッチします。「=SEARCH(“商品*”,A1)」とすれば、「商品」で始まる任意の文字列を検索できます。
FIND関数とSEARCH関数の比較
FIND関数
・完全一致検索
・正確な検索
SEARCH関数
・ワイルドカード可
・柔軟な検索
| 検索対象 | FIND関数 | SEARCH関数 | 結果の違い |
|---|---|---|---|
| Excel(対象:Excel) | =FIND(“excel”,”Excel”) | =SEARCH(“excel”,”Excel”) | FINDはエラー、SEARCHは1 |
| 商品A(対象:商品?) | =FIND(“商品?”,”商品A”) | =SEARCH(“商品?”,”商品A”) | FINDはエラー、SEARCHは1 |
| 東京都(対象:*都) | =FIND(“*都”,”東京都”) | =SEARCH(“*都”,”東京都”) | FINDはエラー、SEARCHは1 |
| TOKYO(対象:tokyo) | =FIND(“tokyo”,”TOKYO”) | =SEARCH(“tokyo”,”TOKYO”) | FINDはエラー、SEARCHは1 |
検索結果を活用して文字列を抽出する
FIND関数やSEARCH関数で得られた位置情報は、LEFT関数やMID関数と組み合わせることで、特定の文字列を抽出できます。
例えば、メールアドレスから@より前のユーザー名部分だけを取り出したい場合、A1セルに「user@example.com」が入っているとき、B1セルに「=LEFT(A1,FIND(“@”,A1)-1)」と入力します。FIND関数で@の位置を特定し、その1つ前までの文字をLEFT関数で取り出すことで、「user」という結果が得られます。
逆に@より後ろのドメイン部分を取り出したい場合は、「=MID(A1,FIND(“@”,A1)+1,100)」とします。FIND関数で@の位置を特定し、その1つ後ろからMID関数で文字列を取り出します。長さを100文字と多めに指定しておけば、実際の文字列が何文字であっても最後まで取得できます。
| A列の値 | 数式 | 結果 | 説明 |
|---|---|---|---|
| user@example.com | =LEFT(A1,FIND(“@”,A1)-1) | user | @より前を抽出 |
| user@example.com | =MID(A1,FIND(“@”,A1)+1,100) | example.com | @より後ろを抽出 |
| 東京都-渋谷区-1-2-3 | =LEFT(A1,FIND(“-“,A1)-1) | 東京都 | 最初の-より前を抽出 |
| 商品番号:A-001 | =MID(A1,FIND(“:”,A1)+1,100) | A-001 | :より後ろを抽出 |
FIND関数やSEARCH関数は、文字列の位置を特定するだけでなく、他の関数と組み合わせることで強力なデータ抽出ツールになります。
特にメールアドレスや住所、商品コードなど、一定のルールで区切られたデータを分割する際に非常に便利です。
ただし、検索文字列が見つからない場合はエラーになるため、IFERROR関数で囲んでエラー処理を行うことをおすすめします。
「=IFERROR(FIND(“@”,A1),”見つかりません”)」とすれば、エラーの代わりに「見つかりません」と表示されます。
また、複数の区切り文字が含まれる場合、FIND関数は最初に見つかった位置しか返さないため、2番目以降の位置を探す場合は開始位置パラメータを活用する必要があります。
検索ダイアログを使った対話的な文字検索
続いては、Excelの標準機能である検索ダイアログを使った検索方法を確認していきます。
基本的な検索ダイアログの使い方
Excelで最も手軽に文字を検索する方法は、検索ダイアログを使用することです。
Ctrl+Fキーを押すか、「ホーム」タブの「検索と選択」から「検索」を選択すると、「検索と置換」ダイアログが開きます。
「検索する文字列」のボックスに探したい文字を入力して「次を検索」をクリックすると、該当するセルが順番に選択されます。「すべて検索」をクリックすれば、該当するすべてのセルの一覧が表示され、リスト内のセルをクリックすることで該当箇所に直接ジャンプできます。
検索ダイアログの基本操作
Ctrl+Fキーを押す
（検索ダイアログ起動）
検索文字列を入力
（例：「東京」）
「次を検索」または
「すべて検索」
検索ダイアログの「オプション」ボタンをクリックすると、詳細な検索条件を設定できます。「検索場所」で「シート」または「ブック」を選択すれば、検索範囲を広げられます。「検索方向」で「行」または「列」を選択すると、検索の順序を変更できます。
「大文字と小文字を区別する」にチェックを入れると、「Excel」と「excel」が別の文字列として扱われます。「セル内容が完全に同一であるものを検索する」にチェックを入れると、部分一致ではなく完全一致での検索になります。例えば「東京」で検索した場合、チェックなしなら「東京都」もヒットしますが、チェックありなら「東京」のみがヒットします。
| オプション項目 | 設定内容 | 効果 |
|---|---|---|
| 検索場所 | シート/ブック | 現在のシートのみ、またはブック全体を検索 |
| 検索方向 | 行/列 | 行方向優先、または列方向優先で検索 |
| 大文字小文字 | チェックあり/なし | 区別する/しない |
| 完全一致 | チェックあり/なし | 完全一致/部分一致 |
ワイルドカードを使ったあいまい検索
検索ダイアログでは、ワイルドカード文字を使って柔軟な検索ができます。
ワイルドカードを使用するには、オプションで「ワイルドカードを使用する」にチェックを入れる必要があります。「?」は任意の1文字を表し、「*」は任意の文字列（0文字以上）を表します。
例えば、「商品?」で検索すると、「商品A」「商品1」「商品X」などがヒットします。「商品??」とすれば、「商品AB」「商品01」など、商品の後ろに2文字が続くものがヒットします。
「*東京*」で検索すると、「東京」を含むすべての文字列がヒットします。「東京都」「大東京」「新東京タワー」などが該当します。「東京*」とすれば、「東京」で始まる文字列のみがヒットします。
ワイルドカード検索のパターン
任意の1文字
→ 商品A、商品1
任意の文字列
→ 大東京、東京都
前方一致
→ 東京都のみ
| 検索文字列 | マッチする例 | マッチしない例 |
|---|---|---|
| 商品? | 商品A、商品1、商品X | 商品AB、商品、商品番号 |
| 商品* | 商品A、商品、商品番号123 | A商品、新商品 |
| *区 | 渋谷区、中央区、北区 | 区画、区別 |
| 20??-??-?? | 2024-01-15、2023-12-31 | 2024-1-5、24-01-15 |
書式や数式を含む高度な検索
検索ダイアログのオプションには、「検索対象」という項目があり、数式や値、コメントを選択できます。
通常は「数式」が選択されており、セルに表示されている内容で検索されます。「値」を選択すると、数式の結果として表示されている値で検索されます。例えば、セルに「=A1&”様”」という数式が入っていて「山田太郎様」と表示されている場合、「数式」では検索にヒットしませんが、「値」なら「山田」で検索するとヒットします。
また、「書式」ボタンをクリックすると、特定の書式が設定されているセルを検索できます。例えば、赤色のフォントが設定されているセルだけを検索したり、特定の塗りつぶし色が設定されているセルを検索したりできます。これは条件付き書式が適用されているセルを見つける際にも便利です。
検索ダイアログを使った検索は、視覚的に結果を確認しながら作業できるメリットがあります。
「すべて検索」の結果リストには、セルの位置だけでなく、その値や数式も表示されるため、どのセルがヒットしたのか一目で確認できます。
また、検索結果のリストからセルを選択すると、そのセルに直接ジャンプするため、大量のデータの中から目的のセルを素早く見つけられます。
ただし、検索ダイアログによる検索は対話的な操作であり、検索結果を他の処理に利用することはできません。
検索結果に基づいて自動的にデータを抽出したり、計算したりする場合は、関数を使った検索方法を選択する必要があります。
後ろから文字を検索する高度なテクニック
続いては、通常とは逆方向から文字を検索する方法を確認していきます。
最後に出現する文字の位置を特定する
文字列の中に同じ文字が複数含まれている場合、FIND関数やSEARCH関数は最初に見つかった位置しか返しません。
最後に出現する文字の位置を特定するには、SUBSTITUTE関数と組み合わせた工夫が必要です。
例えば、「C:\Users\Documents\file.txt」というファイルパスから、最後の「\」の位置を見つけて、ファイル名部分だけを取り出したい場合を考えます。
FIND関数だけでは最初の「\」しか見つけられないため、別のアプローチが必要です。
最後の出現位置を見つける数式は「=FIND(“@”,SUBSTITUTE(A1,”\”,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))」のようになります。
この数式は少し複雑ですが、まずSUBSTITUTE関数で文字列内の「\」の個数を数え、最後の「\」だけを「@」のような別の文字に置き換えてから、その位置をFIND関数で見つけるという仕組みです。
| A列の値 | 数式 | 結果 | 説明 |
|---|---|---|---|
| C:\Users\file.txt | =FIND(“@”,SUBSTITUTE(A1,”\”,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””)))) | 9 | 最後の\の位置 |
| user@test@example.com | =FIND(“@”,SUBSTITUTE(A1,”@”,”#”,LEN(A1)-LEN(SUBSTITUTE(A1,”@”,””)))) | 11 | 最後の@の位置 |
| 商品-A-001-X | =FIND(“@”,SUBSTITUTE(A1,”-“,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””)))) | 10 | 最後の-の位置 |
この数式の仕組みを詳しく説明すると、「LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))」の部分で、元の文字列の長さから「\」を全て削除した文字列の長さを引くことで、「\」の個数を数えています。その個数分だけSUBSTITUTE関数で最後の「\」を「@」に置き換え、FIND関数で「@」の位置を探すことで、最後の「\」の位置が特定できます。
後ろから数えて特定の位置の文字を取得
文字列の後ろから数えて特定の文字を取得したい場合は、RIGHT関数を使用します。
RIGHT関数は「=RIGHT(対象文字列,文字数)」という構文で、文字列の右端から指定した文字数を取得します。
例えば、A1セルに「商品コード-A-001」が入っている場合、B1セルに「=RIGHT(A1,3)」と入力すれば、右から3文字の「001」が取得できます。「=RIGHT(A1,5)」とすれば「A-001」という結果になります。
RIGHT関数による後方からの取得
右から3文字
→ 001
右から5文字
→ A-001
最後の区切り文字より後ろの部分を取得したい場合は、先ほどの最後の出現位置を見つける数式と組み合わせます。
「=RIGHT(A1,LEN(A1)-FIND(“@”,SUBSTITUTE(A1,”-“,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””)))))」とすれば、最後の「-」より後ろの部分だけを取得できます。
| A列の値 | 数式 | 結果 |
|---|---|---|
| C:\Users\file.txt | =RIGHT(A1,LEN(A1)-FIND(“@”,SUBSTITUTE(A1,”\”,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”\”,””))))) | file.txt |
| 商品-A-001 | =RIGHT(A1,LEN(A1)-FIND(“@”,SUBSTITUTE(A1,”-“,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”-“,””))))) | 001 |
| 東京都/渋谷区/1-2-3 | =RIGHT(A1,LEN(A1)-FIND(“@”,SUBSTITUTE(A1,”/”,”@”,LEN(A1)-LEN(SUBSTITUTE(A1,”/”,””))))) | 1-2-3 |
TEXTSPLIT関数による新しいアプローチ
Excel 365やExcel 2021以降では、TEXTSPLIT関数を使って文字列を区切り文字で分割できます。
TEXTSPLIT関数は「=TEXTSPLIT(対象文字列,列区切り文字,行区切り文字)」という構文で、指定した区切り文字で文字列を分割し、複数のセルに展開します。
例えば、A1セルに「東京都/渋谷区/1-2-3」が入っている場合、「=TEXTSPLIT(A1,”/”)」と入力すれば、「東京都」「渋谷区」「1-2-3」が横方向に3つのセルに分割されます。
最後の要素だけを取得したい場合は、INDEX関数と組み合わせて「=INDEX(TEXTSPLIT(A1,”/”),1,COUNTA(TEXTSPLIT(A1,”/”)))」とすることで、最後の「1-2-3」だけを取得できます。
後ろから文字を検索する処理は、ファイルパスからファイル名を取り出す、URLからドメイン部分を抽出する、階層構造のデータから最下層の要素を取得するなど、実務でよく使われるテクニックです。
従来の関数を使った方法は数式が複雑になりがちですが、一度理解すれば応用が効きます。
Excel 365やExcel 2021以降をお使いの場合は、TEXTSPLIT関数を活用することで、より直感的に文字列を分割できます。
ただし、TEXTSPLIT関数は複数のセルに結果を展開するスピル機能を使用するため、右側や下側のセルが空いている必要があります。
既にデータが入力されているセルに展開しようとするとエラーになるため、注意が必要です。
まとめ エクセルで文字を検索(後ろから:セル内の特定:あいまい検索も)
エクセルで文字を検索する方法をまとめると
・FIND関数とSEARCH関数による検索：「=FIND(検索文字列,対象文字列)」で位置を取得、FIND関数は大文字小文字を区別し、SEARCH関数は区別せずワイルドカードも使用可能、LEFT関数やMID関数と組み合わせて文字列を抽出
・検索ダイアログによる対話的検索：Ctrl+Fで起動し、ワイルドカード「?」「*」を使ったあいまい検索が可能、「すべて検索」で該当セルを一覧表示、書式や数式を対象にした検索も実行可能
・後ろからの検索：SUBSTITUTE関数とLEN関数を組み合わせて最後の出現位置を特定、RIGHT関数で後方から指定文字数を取得、TEXTSPLIT関数で区切り文字による分割が可能（Excel 365以降）
・検索結果の活用：ISNUMBER関数やIFERROR関数で検索結果の有無を判定、INDEX関数やMATCH関数と組み合わせて高度な検索が実現
これらの方法にはそれぞれメリットがあり、状況に応じた使い分けが重要です。
単純に文字の位置を知りたい場合はFIND関数やSEARCH関数、視覚的に確認しながら検索したい場合は検索ダイアログ、検索結果を他の処理に利用したい場合は関数の組み合わせが適しています。
ただし、大量のデータを処理する際は注意が必要です。
複雑な数式は計算負荷が高くなるため、データ件数が多い場合はパフォーマンスに影響することがあります。
Excelの文字検索テクニックを適切に活用して、データ分析作業の効率化を実現していきましょう！
