「つばさ」から「川越」を取り出す方法
前回の続きです。
前回の分をまだ、読んでない方は、こちらからどうぞ↓
前回は、 「埼玉県川越市」から、
埼玉県を切り出す事ができましたね。
では、次に、「川越市」を 切り出してみましょう
「埼玉県」を切り出したときの手法を踏まえて
やるとなると、
「県」 の文字は、 左から3文字目にある事が
わかっているから、
「川越市」 は、その次、
つまり、
４つめから、切り出せば、いいことになりますね。
4つめ、 と言うことは、
「３」に １を足せばいいのです。
よって、
=MID(A1,FIND("県",A1)+1,
ここまでは、わかりますか
A1 の セル の 左から４文字目から
（つまり、「県」 =3 ですから、 これに ＋１ で
4文字目から、 という 数式にしてあるわけです）
さて、数式は、これで完成ではありません。
何文字目から、 は、確定しましたが
何文字分、取り出すか
が問題です。
「川越市」は、3文字だから
３ を指定すればいいのでしょうが
ほかの市だったら、どうするか？
3文字以上の市もありますよね
「市」のあとに、 町名や番地なども
入ったとすると、
ますます、何文字分切り出していいか、
わからなくなりませんか？
「わからないから、とりあえず、
最大値１００ ぐらいにしておけば、
いいんじゃないの？」
こういう考え方もありです。
たしかに、住所で１００文字以上、って
なさそうです。
でも、この技を、他に応用したいときは、
データが、「住所」 とは限りませんから
最大値をいくつにしておけばいいのか、
わからないものもありますよね
他に、切り出す方法は、ないものか、、、？
そこで、
=SUBSTITUTE
という関数を使ってみましょうか
この関数、 文字を切り出す関数では
ありません。
文字の置き換えを行うときに使う関数です。
じゃ、切り出しは出来ないの？
「できません」
じゃ、使えないんじゃ、 ないの？
たしかに、使えません
でも、
発想の転換で、
「埼玉県川越市」
の 「埼玉県」 を
ブランクに置き換える、としたら、どうでしょう。
「埼玉県」は、 ブランクになってしまいますから、
「川越市」だけが残る、ということになりませんか？
でも、
ブランクに置き換えって、
こんな風になっちゃうんじゃないの？
「埼玉県」 の代わりに、 3文字分のブランクが入る、
こんなイメージを持たれる方も、いらっしゃるかもしれません。
そこで、置き換えるのは、 ０文字分のブランクです。
長さゼロですから、 結果的に 「川越市」だけが、残る、
ように見えます。
数式は、こうなります。
Ｄ１ の 数式を見てください。
=SUBSTITUTE(A1,C1,"")
A1セルの 文字のうち、 C1セルにある文字に相当する部分を "" (ブランク）
に置き換えなさい。
という意味です。
これで、 Ｄ１には、 「川越市」 と表示されるようになります。
文字数が何文字だろうが、関係ありません。
「何文字分切り出せ」 という数式ではありませんから。
これで、とりあえず、解決したように見えますが、
この数式は、置き換え対象文字として、Ｃ１ を見に行ってますね。
つまり、
Ｃ１に 「埼玉県」 という文字が、切り出されている事を
前提としているわけです。
すると、県名が切り出させてないときに、「市」 だけ切り出そうとした場合、
エラーが起きてしまいます。
そこで、 Ｄ１の数式に、 Ｃ１の数式も組み込んでしまいましょう。
=SUBSTITUTE(A1,MID(A1,1,B1),"")
これで、Ｃ１のセルがどうなっていようと、 Ｃ１に依存しないで
ちゃんと「川越市」を切り出す事が出来ます。
このわざは、
たとえば、
○○部△△課 を
「○○部」 と 「△△課」 に分けたい、 と言った場合にも
応用が出来ますね
*****************************************************************
今回のポイントは、 ココ
=SUBSTITUTE(元の文字列, 置き換えたい文字 ,何に置き換えるか)
=SUBSTITUTE(A1,C1,"")
"" の " と " の 間の間隔が、
置き換えるブランクの長さ となります。
"" のように、ぴったりくっつけると、 ０文字分のブランクとなり
" " のように、3文字分あけると、
このような結果になります。
*******************************************************************************
今回の SUBSTITUTE の 使い道のように
本来は、文字を置き換えする関数なのに、
工夫しだいで、文字切り出しに利用できちゃったり
EXCELは、アイデアしだいで
用意されている関数を、別の用途で使って
みることによって、無限の可能性を
引き出すことが出来る、非常に奥が深い
ツールである。
だから、おもしろい。
このおもしろさを、多くの人に
わかってもらいたいし、
このおもしろさに はまる人たちを
ふやしたい
それが、私がブログをやっている理由であり、
夢でもあるわけです。