Excelで特定の文字以降を抽出したいと思っても、少し難しいケースがあります。
例えば、
- ある文字列の中に特定の文字が複数個含まれている
- 特定の文字が何個含まれているかは決まっていない
という場合です。
例えば特定の文字（例えばコロン「：」）が文字列の中に必ず1つだけ入っているというような場合、：より後を抽出するのは難しくありません。しかし、特定の文字が複数個、さらに個数が決まっていないようなケースの場合、難易度は劇的に上がります。
なぜなら、Excelには右から特定の文字までの位置を抽出する関数が無いためです。
最後に出てきた特定の文字（：）より後を抽出したい
| No | 元の文字列 | 抽出したい文字列 |
|---|---|---|
| １ | あ：い | い |
| ２ | あ：い：う | う |
| ３ | あ：い：う：え | え |
| ４ | あいうえお | ＜空文字＞ |
No1～４について、全て同じ数式を使って抽出したいところですが、パッと考えても難しい。特定の文字より前を取得するなら簡単なのに・・・
そこでこの記事では、VBAを使わずにExcel関数のみを使用して、右から特定の文字までを抽出する方法をまとめました。
言い換えると、
- 後ろから特定の文字までを検索し、一番初めにヒットした位置までを抽出する
- 一番最後に出てきた特定文字から文末までの文字を抽出する
という手順をこの記事ではまとめました。
特定の文字以降を抽出するサンプルExcel
以降の記事では、Excel関数を使って右から特定の文字までを抽出する際の考え方や手順を解説していますが、よくわからない方のためにサンプルExcelを用意しました。
サンプルExcelは、A列に元の文字列、B列に特定の文字を入力すると、I列に右から特定の文字までを抽出した結果が表示されるようなものになっています。
ダウンロードは以下のボタンから行えますので、記事の解説と合わせて参考にしてみてください。
右から特定の文字までを抽出する際の考え方
Excelには右から特定の文字までの位置を抽出する関数は用意されていません。
そのため、実現するには工夫が必要となります。
「文字列を左右反転させて左から特定文字までの位置を調べ抽出後、再度左右反転させる」という考えが次に思い浮かぶのですが、残念ながら文字列を左右反転する関数もExcelにはありません（無理やりやれば可能だが現実的ではない）。
そこで、右から特定の文字までを抽出するためには、「 一番最後に出てきた特定文字から文末までの文字を抽出する」という考え方でアプローチを取っていきます。
- 一番最後に出てきた特定の文字を別のユニークな文字に置換する（例えば■など）し、位置を取得
- ■以降の文字を抽出
ポイントは上記１の「一番最後に出てきた特定の文字の位置を検索する」です。
右から特定の文字までを抽出する手順
右から特定の文字までを抽出するために必要なのは、一番最後に出てきた特定の文字の位置を取得することです。ここが実現できれば、後はもう実現できたも同然です。
ここでは、次の文字列を例に解説していきます。
【A列】元の文字列＝あいうえお：かきくけこ：さしすせそ
【B列】特定の文字＝：（コロン）
【I列】取得したい文字＝さしすせそ
一番最後に出てきた特定の文字の位置を取得
特定の文字が最後に出現した位置を取得するには、次の考え方で進めていきます。
- 元の文字列の長さを取得
- 元の文字列から特定の文字を除いた長さを取得
- 特定の文字の個数が算出される（１－２）
- 一番最後の特定文字を別のユニーク文字に置換
- ユニーク文字の位置を取得
元の文字列の長さを取得
元の文字列は「あいうえお：かきくけこ：さしすせそ」なので、LEN関数を使用して算出します。
=LEN("あいうえお：かきくけこ：さしすせそ")
または
=LEN(A2)
この結果は17となります。
元の文字列から特定の文字を除いた長さを取得
次に元の文字列から特定の文字（：）を除いた長さを求めますが、SUBSTITUTE関数を併用します。
=LEN(SUBSTITUTE("あいうえお：かきくけこ：さしすせそ", "：", ""))
または
=LEN(SUBSTITUTE($A2,$B2,""))
SUBSTITUTE関数で、元の文字列の中に含まれる特定の文字（：）を空文字に変換するということを行っています。
この結果は15となります。
よって、最終的に特定の文字の個数は、2個（17 - 15）と算出できました。
元の文字列の中に特定の文字がいくつ含まれるか分からない場合、この方法を使うことで個数を算出することができます。
一番最後に出てきた特定の文字を別のユニーク文字に置換
次に、一番最後に出てきた特定の文字を、別のユニーク文字に置換するということを行います。
=SUBSTITUTE( "あいうえお：かきくけこ：さしすせそ" ,"：", "■", 2)
または
=SUBSTITUTE($A2,$B2,$C2,$F2)
結果は次のように、元の文字列の中で一番最後にある特定の文字が、ユニーク文字（ここでは■）に変換されました。
ポイントはSUBSTITUTE関数の第4引数で、何個目に見つかった特定の文字を置換するかを指定しているところです。
上記の例では特定の文字「：」は2個あり、一番最後（2個目）に対してのみユニーク文字へ置換したいため、第4引数で2を指定しています。
ユニーク文字の位置を取得
ユニーク文字の位置を取得します。
=FIND( "■" , "あいうえお：かきくけこ■さしすせそ")
または
=FIND($C2,$G2)
これで、一番最後に出てきた特定の文字の位置を取得できました。（上記の例の場合は12）
右からユニーク文字までを抽出する
最後に、右から特定の文字（ユニーク文字）までを抽出します。
=RIGHT("あいうえお：かきくけこ■さしすせそ", 17-12)
または
=RIGHT($G2, $D2-$H2)
これで、右から特定文字までを抽出することができました。
サンプルExelにあるように、この方法を使えば元の文字列の中に特定の文字「：」が何個あっても、右から特定の文字でを抽出することが可能です。
まとめ
Excel関数を使用して、右から特定の文字までを抽出する方法をご紹介しました。
特に難しいのが、元の文字列の中に特定の文字が複数含まれている場合を考慮することです。
ご紹介したExcelサンプルを使うと簡単に実現できますので、サンプルを参考に自分がやりたい形に変えて使ってみて下さい。
また、この記事に書いたようなちょっとした困ったことでも、自分に経験や知識が足りないために対応できなかったり、時間をかけたわりに全然進まなかったりと、思うようにいかない事ってありますよね。そんなときは専門家に相談したり解決してもらったりする方法もありますので、一人で悩まず対処してみましょう。
