Excelで後ろから検索するFIND関数の使い方をお探しですね。

広告

Excelで文字列を「右から検索」する方法──FIND関数の裏ワザ解説

Excelでファイル名や商品コード、メールアドレスなんかを扱っていると、「最後のハイフンより後ろだけ取り出したいな」とか「右から見て最初に出てくる記号がどこにあるか知りたい」って場面、けっこうありますよね。

でも困ったことに、FIND関数って基本的に左から検索する関数なので、そのままだと「右から検索」ができないんです。

この記事では、FIND関数にLEN関数やSUBSTITUTE関数、RIGHT関数なんかを組み合わせて、文字列を右から検索・抽出する実務向けの裏ワザを分かりやすく解説していきます。

1. FIND関数は右から検索できない──でも工夫すれば「最後の位置」が見つかる

ExcelのFIND関数は、指定した文字列が対象の文字列の左から何文字目にあるかを教えてくれる関数です。

基本の形は「=FIND(検索文字列, 対象, [開始位置])」。

たとえばA1セルに「ABC-123-XYZ」って入っている場合、「=FIND(“-“, A1)」と入力すると、最初に見つかったハイフンの位置である「4」が返ってきます。

ここで押さえておきたいのは、**FIND関数の開始位置を指定しても、検索方向はあくまで左から右**ってこと。

右端から左へ向かって検索する機能は、残念ながら用意されていないんですね。

だから文字列の中に区切り文字が1つだけならFIND関数だけで十分なんですが、「2024-営業-売上.xlsx」とか「AA-BB-CC-DD」みたいに同じ記号が何個もあるデータだと、最初の記号じゃなくて**最後の記号を基準にしたい**ケースが出てきます。

たとえば「AA-BB-CC-DD」から最後のハイフンより後ろの「DD」だけを取り出したいとき、普通のFIND関数だと最初のハイフンを見つけちゃうので、思ったような結果になりません。

じゃあどうするか？

実は考え方を変えればいいんです。

「右から検索する」んじゃなくて、**「最後に出てくる検索文字だけを別の目印に置き換えて、その目印をFIND関数で探す」**って方法を使います。

つまりFIND関数そのものを右向き検索に変えるんじゃなくて、SUBSTITUTE関数で最後の区切り文字だけを一時的に特殊な記号へ置き換えて、その位置をFIND関数で調べるわけです。

この発想さえ押さえておけば、ファイルの拡張子とか商品コードの末尾、住所や部署名の末尾要素なんかを取り出すときに、いろいろ応用できますよ。

2. 右から特定の文字を検索する基本の式と仕組み

右から検索したいときの基本の式は、最後に出てくる検索文字を一時的な目印に置き換えて、その目印の位置をFIND関数で取得する形になります。

A1セルに「AA-BB-CC-DD」があって、最後の「-」の位置を知りたい場合は、次の数式を使います。

“`excel
=FIND(“★”, SUBSTITUTE(A1, “-“, “★”, LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”))))
“`

ちょっと長くて難しそうに見えますが、順番に見ていけば大丈夫です。

まず「**LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”))**」の部分で、A1セル内にハイフンが何個あるかを数えています。

LEN関数は文字数を数える関数で、SUBSTITUTE関数は指定した文字を別の文字に置き換える関数です。

元の文字列の文字数から、ハイフンを空文字（何もない状態）に置き換えた後の文字数を引くことで、消えたハイフンの数、つまり**ハイフンの出現回数**が分かるんですね。

次に、SUBSTITUTE関数の4つ目の引数にその出現回数を指定します。

実はSUBSTITUTE関数って、「何番目に出てくる文字だけを置換するか」を指定できるんです。

だからハイフンが3個あるなら3番目のハイフン、つまり**最後のハイフンだけを「★」に置き換えられる**ってわけ。

その結果、「AA-BB-CC★DD」みたいな文字列が内部的に作られて、最後にFIND関数で「★」の位置を探すことで、最後のハイフンの位置を取得できる仕組みです。

注意点：目印の記号は慎重に選ぼう

目印に使う「★」は、**元の文字列に含まれていない文字を選ぶ**必要があります。

もし対象のデータにすでに「★」が含まれていると、FIND関数が本来の目印じゃない場所を見つけちゃう可能性があるんですね。

実務では「★」「●」「§」なんかの、データ内に出にくい記号を使うことが多いです。

ただ商品名とか備考欄みたいに自由入力が多いデータだと、事前に含まれていないことを確認しておくと安心ですよ。

3. 最後の区切り文字より後ろ・前を抽出する実用例

最後の区切り文字の位置が分かれば、RIGHT関数やLEFT関数と組み合わせて、右側とか左側の文字列を抽出できます。

最後の区切り文字より後ろを取り出す

たとえばA1セルに「AA-BB-CC-DD」と入っていて、最後のハイフンより後ろの「DD」を取り出したいときは、次の数式を使います。

“`excel
=RIGHT(A1, LEN(A1)-FIND(“★”, SUBSTITUTE(A1, “-“, “★”, LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”)))))
“`

この数式では、さっきのFIND部分で最後のハイフンの位置を求めています。

文字列全体の長さから最後のハイフンの位置を引くと、最後のハイフンより右側にある文字数が分かりますよね。

その文字数をRIGHT関数に渡すことで、右端から必要な文字数だけを抽出できるわけです。

ファイル名から拡張子を取り出すとか、コード体系の末尾分類を取り出すとか、URLの最後の階層を抜き出すとか、そういう処理に向いています。

最後の区切り文字より前を取り出す

反対に、最後の区切り文字より前を取り出したい場合は、LEFT関数を使います。

A1セルの「AA-BB-CC-DD」から「AA-BB-CC」だけを取り出すなら、次のように指定します。

“`excel
=LEFT(A1, FIND(“★”, SUBSTITUTE(A1, “-“, “★”, LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”))))-1)
“`

LEFT関数では左端から指定文字数分を取り出します。

最後のハイフン自体は不要なので、FIND関数で求めた位置から1を引いています。

これで最後の区切り文字の直前までをきれいに抽出できます。

たとえば「部署名-担当者名」とか「商品カテゴリ-商品名-サイズ」みたいに、末尾の要素だけを削除したいデータ整理でも便利ですよ。

ファイル名から拡張子を抽出する例

ファイル名から拡張子を抽出する例もよく使われます。

A1セルに「sales.report.2024.xlsx」みたいにピリオドが複数含まれている場合、普通のFIND(“.”, A1)みたいな考え方だと最初のピリオドを見つけちゃいます。

最後のピリオドを基準にするには、検索文字を「.」に置き換えて、次のように使います。

“`excel
=RIGHT(A1, LEN(A1)-FIND(“★”, SUBSTITUTE(A1, “.”, “★”, LEN(A1)-LEN(SUBSTITUTE(A1, “.”, “”)))))
“`

この式なら、途中にピリオドがいくつあっても、最後のピリオドより後ろにある「xlsx」を抽出できます。

拡張子にドットを含めたい場合は、RIGHT関数で取り出す文字数を1文字分増やすなど、目的に応じて調整してください。

4. エラー対策と応用のコツ──実務で使いやすくするために

この裏ワザを実務で使うときは、**検索文字が存在しない場合のエラーに注意**が必要です。

FIND関数は、指定した文字が見つからないと「#VALUE!」エラーを返します。

たとえばハイフンが入っている前提で数式を作ったのに、A1セルに「AABBCCDD」みたいなデータが混ざっていると、最後のハイフンを探せずエラーになっちゃうんですね。

入力データにばらつきがある場合は、**IFERROR関数でエラー時の表示を指定**しておくと扱いやすくなります。

“`excel
=IFERROR(RIGHT(A1, LEN(A1)-FIND(“★”, SUBSTITUTE(A1, “-“, “★”, LEN(A1)-LEN(SUBSTITUTE(A1, “-“, “”))))), “”)
“`

この式では、抽出に失敗した場合は空白を返します。

空白じゃなくて「区切り文字なし」とか、元の文字列をそのまま返す形にしても構いません。

大事なのは、数式が正しくても元データの形式が必ずしも統一されているとは限らないので、エラーが出たときにどう見せるかまで決めておくことです。

特に他の人に渡すExcelファイルでは、エラー表示をそのまま残すよりも、IFERROR関数で処理したほうが読みやすくなりますよ。

FIND関数とSEARCH関数の違い

ちなみにFIND関数とSEARCH関数の違いも押さえておくと便利です。

**FIND関数は大文字と小文字を区別**して、ワイルドカードには対応しません。

一方、**SEARCH関数は大文字と小文字を区別しない**ので、「A」と「a」を同じものとして探したい場合に向いています。

ハイフンやピリオドなんかの記号を探す場合はどっちでも大きな差はないんですが、英字を検索対象にする場合は、区別したいならFIND、区別しなくてよいならSEARCHって考えると分かりやすいです。

検索文字列が2文字以上の場合

検索文字列が2文字以上の場合は、出現回数の数え方にちょっと工夫が必要です。

たとえば「–」とか「::」みたいな複数文字の区切りを探す場合、文字数の差をそのまま使うと、区切り文字の長さ分だけ数が大きくなっちゃいます。

その場合は、差分を検索文字列の文字数で割って出現回数を求めます。

検索文字列をB1セルに入れて管理するなら、次のような考え方です。

“`excel
=FIND(“★”, SUBSTITUTE(A1, B1, “★”, (LEN(A1)-LEN(SUBSTITUTE(A1, B1, “”)))/LEN(B1)))
“`

新しいExcelならもっと簡単な方法も

Microsoft 365やExcel 2021以降を使っている場合は、**TEXTAFTER関数**で「最後の区切り文字より後ろ」を簡単に取り出せることもあります。

たとえば「=TEXTAFTER(A1, “-“, -1)」とすれば、最後のハイフンより後ろを抽出できます。

ただしすべてのExcel環境で使えるわけじゃないので、古いバージョンとか共有先の環境を考えるなら、FIND関数、SUBSTITUTE関数、LEN関数を組み合わせた方法を覚えておく価値がありますよ。

まとめ

Excelで文字列を「右から検索」したいときは、FIND関数単体で解決しようとせず、**最後の検索文字だけを目印に置き換える発想**がポイントです。

– 位置を調べたいだけなら → **FINDとSUBSTITUTE**
– 右側を抽出したいなら → **RIGHT**も組み合わせる
– 左側を残したいなら → **LEFT**を組み合わせる

仕組みさえ理解しておけば、電話番号、商品コード、ファイル名、URL、部署名など、区切り文字を含むいろんなデータ整理に応用できます。

Excelの文字列処理で作業時間を減らしたいって人は、まずこの「最後の区切り文字を探す」パターンを覚えておくと実務でめちゃくちゃ役立ちますよ。

広告