情報科学屋さんを目指す人のメモ

方法・手順・解説を書き残すブログ。私と同じことを繰り返さずに済むように。

【Excel】FIND関数で一番右の文字位置を調べる方法(区切り文字の右端を切り出す方法)

Excel (25) Excel 2016 (15) Excel-FIND (1) Excel-LEN (1) Excel-REPT (1) Excel-RIGHT (1) Excel-SUBSTITUTE (1) Excel-TRIM (1) Excel-文字列操作 (1)

Excelには、FIND関数という、指定した文字が、文字列の先頭から何番目にあるのか、を計算してくれる重要関数があります。

Excelの文字列操作の中では、とても重要な関数で、文字の切り出しを行うにしても、まずFIND関数で場所を特定する必要があったりします(Excelの文字列操作は、原始的な操作の組合せで行う)。

しかしこのFIND関数は、同じ文字が複数存在した場合、一番左にある文字の位置を取得してしまい(左から探す)、「一番右」にある文字の位置を知る(右から探す)ことはできず、「ある文字で区切ったときの、一番最後の部分を抜き出す」ような操作は少し手間がかかります

というわけで今回、一番右の区切り文字の位置をFINDする方法を紹介します。VBAは使いません

FIND一発ではできない

今回は、「A1」セルに入力された文字列(例:「スマートフォン>iPhone>iPhone 7 Plus」)から、「>」を区切り文字としたときの一番右(例:「iPhone 7 Plus」)を取り出すことを目標にします。

通常、「>」が1回しか登場しないのであれば、FIND関数で「>」の位置を調べて、RIGHT関数で、それより後ろを切り出す、ということをすればOKです。

しかし今回の場合は、普通に「>」の位置を調べるFIND関数を「=FIND(">", A1)」のようにしてしまうと、一番左の「>」の位置(7)を取得してしまい、そのままRIGHTをしても、結果が「iPhone>iPhone 7 Plus」になってしまいます。

区切り文字の出現回数を知る

こんなとき重要なのるのが、一番右にある区切り文字(>)の位置(15)を調べる「FIND」の作り方(今回の本題)です。

複数のステップが必要なので、順に説明します。

まず最初に、対象セル(A1)に、区切り文字(>)が何回出現するか(何文字あるか)を調べます。

これは、「A1の文字数」から「A1から『>』を取り除いた文字数」を引き算して求めます。

なので、「B1」に、「=LEN(A1)-LEN(SUBSTITUTE(A1,">",""))」と入力してみます。

「スマートフォン>iPhone>iPhone 7 Plus」には「>」が2つあるので、結果は「2」となります。

「一番右の区切り文字」だけを「ダミー文字」に置換する

最終的に、一番最後(右)の区切り文字の位置を知りたいので、その下準備として、一番右の区切り文字を、ダミー文字(今回は「!」)に置換してしまいます

というのも、文字列置換に使う「SUBSTITUTE」関数には、「何番目に出現したものを置換する」という引数を指定できて都合が良いからです。

先ほど「区切り文字が何個登場するか」を算出しておいたので、その数値をその引数に指定すれば、見事、一番右にある区切り文字(>)を、ダミー文字(!)に置換できます。

「B2」に、「=SUBSTITUTE(A1, ">", "!", B1)」を入力すると、結果は「スマートフォン>iPhone!iPhone 7 Plus」となります。

置換した「ダミー文字」の位置を調べる

そうしたら最後に、「FIND」関数を使って、先ほど設置しておいたダミー文字(!)の位置を調べます。

「B3」に「=FIND("!", B2)」を入力すると、一番右の「>」の位置である「15」が取得できます。

ここまでをまとめると、「=FIND("!", SUBSTITUTE(A1, ">", "!", LEN(A1)-LEN(SUBSTITUTE(A1,">","")))」となります。

RIGHTで切り出す

最後に、指定した位置より後ろの文字を取り出せる「RIGHT」関数で、一番右の「>」より右の部分を取り出します。

RIGHT関数は、右からの文字数で、位置を指定するので、先ほどの左からの文字数「15」を、全体の文字数から引き算する必要があります。

「B4」に「=RIGHT(A1, LEN(A1)-B3)」と入力すれば、最終結果「iPhone 7 Plus」が取得できます。

すべてまとめると「=RIGHT(A1,LEN(A1)-FIND("!", SUBSTITUTE(A1, ">", "!", LEN(A1)-LEN(SUBSTITUTE(A1,">","")))))」となります。

まとめ画像

エラー対策

今回の方法では、区切り文字が1つも存在しない場合にSUBSTITUTEに「『0』番目」を指定してしまい、それ以降「#VALUE!」エラーになってしまうので、「IFERROR(先ほどの式, A1)」のようにして、エラーが無ければ先ほどの式の結果を返し、エラーが発生したら元の文字列(A1)をそのまま返す、としてあげると丁寧です。

また本当は置き換えた文字(!)がもとから存在していると壊れてしまうので、上手に選ぶか、「CHAR」を使ってあり得ない文字を使うかするとより厳密になるかと思います(が、そこまで必要なケースは限られるかと)。

全く別の方法

全く別の方法として、「=TRIM(RIGHT(SUBSTITUTE(A1,">",REPT(" ",LEN(A1))),LEN(A1)))」が「Excel: last character/string match in a string - Stack Overflow」で紹介されています(*REPTは、指定した文字を、指定した回数繰り返した文字列を作り出す関数)。

これは思い付きにくい分だいぶ上手いやり方で、区切り文字を十分長い(LEN(A1))空白文字で置き換えてから、右端LEN(A1)文字を切り出して、ざっくり「(空白たくさん)iPhone 7 Plus」のような文字を切り出し、最後に「TRIM」で余分な空白を除去しています。

区切り文字を置換した空白文字の列の文字数(LEN(A1))と同じ文字数で切り出せば、長く切り出しすぎて、一番右の空白を飛び越えて切り出してしまうことはありません(「iPhone(空白たくさん)iPhone 7 Plus」のようにならない)。また一方で、LEN(A1)は、LEN("iPhone 7 Plus")より絶対に長いので、切り出し範囲が短すぎて、結果が「ne 7 Plus」のようになってしまうこともありません。

ひとこと

気持ちとしてはSPLITという名前だとか、InStrに対するInStrRevのような、FINDに対するFIND.REVでもあればよいのですが、そんな便利なものはなく。COUNTやREPLACEのような名前も出現しません。FIND・LEFT・MID・RIGHT・SUBSTITUTEあたりで頑張れる範囲には驚きです。

参考

コメント(0)

新しいコメントを投稿