さんだーさんだ!(ブログ版)

2015年度より中高英語教員になりました。2020年度開校の幼小中混在校で働いています。

FIND関数で右から検索

エクセルで、例えばある列に

A B
1 委員会名 号数
2 ◯◯委員会・12号
3 □□・××委員会・3号

なんて文字列が入っていて、そいつを

A B
1 委員会名 号数
2 ◯◯委員会 12号
3 □□・××委員会 3号

と分割したい。

「データ」―「区切り位置」から「・」を選択すればいいような気もするけど、そうすると

A B C
1 委員会名 号数
2 ◯◯委員会 12号
3 □□ ××委員会 3号

となってしまいます。委員会名にも「・」が使われているのがネックです。

ここで、「右から『・』を探して、最初の『・』よりも右を取り出す」ということができればほとんど完成だということに気づきます。
が、調べてみるとFIND関数を右から動かす引数はないようです。残念。
が、しかし、さらに調べてみるとやっぱり賢い人は色々考えているみたいで、右から動かす関数(の組み合わせ)を発見しました。
B2セルに入れるべき関数は、以下の通り。

=RIGHT(A2,LEN(A2)-FIND(CHAR(9), SUBSTITUTE(A2,"・",CHAR(9),SUM(LEN(A2),-1*LEN(SUBSTITUTE(A2,"・",""))))))

Excel関数のみで右から検索 - きどたかのブログより。有難うございました。)

まず、SUM(LEN(A2),-1*LEN(SUBSTITUTE(A2,"・","")))で、「・」が含まれている数を出します*1
これを、SUBSTITUTE(A2,"・",CHAR(9),hoge)のhoge部に入れています。
普通SUBSTITUTE関数というと、SUBSTITUTE(対象文字列,置換前文字列,置換後文字列)とやるのですが、第4の引数を設定すると、これは「何番目の[置換前文字列]を置換するか、ということになるらしい(全然知らなかった)。


例:

A B
1 部屋・Tシャツ・私・俺・お前・大五郎 =SUBSTITUTE(A1,"・",CHAR(9),SUM(LEN(A1),-1*LEN(SUBSTITUTE(A1,"・",""))))

とすると、

A B
1 部屋・Tシャツ・私・俺・お前・大五郎 部屋・Tシャツ・私・俺・お前 大五郎

「お前」と「大五郎」の間がタブ(=CHAR(9))になっているみたいです。

あとは、
FIND関数で唯一つのCHAR(9)を探して(FIND(CHAR(9),[今作った文字列])、そこから右を取り出せばOK*2とする。この辺は基本テクニックですね!))、なわけです。

これでB列は完成ですが、A列の中にはまだB列に移動したものも残っています。
なので、うまいことLEFT(A列, LEN(A列)-LEN(B列))とかしてもらえば今度は前の部分が取り出せるので、しっかり全部コピー→値として貼り付けをしてから、元の列を消せばいいでしょう。

いやあ、コピペして使わせていただいた関数も、しっかり見ると奥が深い!

*1:単純にLEN(A2)-SUBSTITUTE(A2,"・","")でやらない辺りが慣れてるっぽい。

*2:RIGHT(LEN(その文字列)-FIND(CHAR(9)の位置