ハイ、ひさびさにExcelの話です。こないだ仕事してて「こういうの、知ってるとみんな便利なんじゃないかな」って思ったのと、自分への備忘録のために。
爆裂ヒンジ 20mm 20個入 BAK-1020MK
トゲトゲ取っ手 2個入 TOGE-DEATH001
みたいに入力されてるとき。おしりに入っている「BAK-1020MK」「TOGE-DEATH001」を抜き出したい。
これね、もしおしりの「BAK-1020MK」部分の文字数が同じだったら、right関数で引っ張れるんですよ。=right(B2,10)とかで。
でも、そうなってはいないわけで…。
セル内はそれぞれ空白(半角スペース)で区切られています。ですが、半角スペースの個数もまちまちです。なので、「区切り位置」使っても後が大変です。
こういうときに、どうするか。
以下のやり方は、フルパスからファイル名を抜き出す(例:C:\Users\知巳\Desktop\aaa\gorilla.xlsxから最後の\の後ろを抜き出す)といったケースにも応用できますので、ご一読いただければ。
---以下、解法---
解決策を一言でまとめると、「空白が何個あるか割り出して、最後の空白だけを別の文字に置換する」です。
順番にやっていきましょう。
まず、各セルの空白の個数を割り出しましょう。
最初は現状の文字数です。
len関数を使えばできますね。
次に、空白を削除した文字数を出します。つまり、[現状の文字数]-[空白を削除した文字数]を出せば、各セル内の空白の個数がわかるな、と。
ということで、空白を削除した文字列を作りたい。ここで、文字をコピーしてCtrl+Hで置換、でもよいのですが、今回はsubstitute関数を使いましょう。
substituteは「置き換える」くらいの意味。使い方は、
=substitute(対象セル, 検索する文字, 置換後の文字)
です。なので、今回は=substitute(B2," ","")としましょう。(最後の""、チョンチョンのあいだは何もいれません。空白を「トル」なので。)
ハイ、空白なくなりました。これを全セル分やっといてください。
んで、空白がなくなった文字列を改めて文字数カウントします。再びlen関数です。
ハイ、以上のこと、今回はていねいにやりましたが、できる人は1セルで一気に計算してもよいですよ。
ハイ、ここまでが準備です。
で、いよいよ。最後の空白を置換します。
実は、substitute関数はもうひとつ引数を設定することができるのです。それは「何個目の文字を置換するか」です。これを省略すると全部置換しちゃうのですが、コイツを設定してやれば…。
ということで、やってみましょう。
このナビゲートに出てくる最後の引数、[置換対象]ってヤツがそれです。
ここに数字を設定すると、「○番目だけ置換する」ができるんです。
さっそく。
=substitute(B2," ",";",C2)と式を入れます。C2セルってのはさっき、空白の個数を出したセルですね。空白の個数、イコール「最後の空白は何番目なのか」ってことになります。
置換後の文字は「;セミコロン」にしました(セミコロンでなくとも、使われてない文字ならOKです)。
なので、式の意味は、「半角スペースをセミコロンに置き換えてください、[C2セルの値]番目のだけ)となります。
ということで…、ほら、最後だけセミコロンになった。
あとは、値貼り付けし、データ→区切り位置でセミコロンを指定すればOK。
はい、できたー。
ワイルドカード置換で、「*;」(アスタリスクとセミコロン)でもいいけど。
以上。
find関数だと、「何番目」ってのが指定できないんですよ。でもsubstituteならそれができる。そこがポイントですね。
ではでは。