「コピぺ de 関数」FILE_02
今回は、セルに入っている文字列を抜き出す色んな方法を解説いたします。
文字列？とは、何かと言いますと、、、一つ一つのセルにとって、中に入っているデータは数字や英語、ひらがな、カタカナ、漢字が並んでいるだけで意味などわかりません。
何かの文字が、何個も連なって入っているだけです、その一つ一つの文字を文字列と言います。（イメージがつけば幸いです）
今回は下記のような時に使えるものをご紹介します。
- もらったデータに「.jpeg」や「20180707」のようにいらないものがくっついてるから取り除きたい
- 都道府県と住所が合体したデータから都道府県だけ抜き出したい
- 「名字 名前」で名字だけ抜き出したい
- 先頭に毎回不要な文字列があるので、先頭の文字を無視して文字列を取り出したい
- 長い文字の中から必要な文字だけを抜き出したい
それでは使い方をお伝えして行きますが、まずは自身のExcelでやって見ましょう。
下記のデータを、ご自身のExcelを開き、A1に貼り付けてください。
| 都道府県 | データ名 | 担当者 | 担当資料 | 社員ID |
|---|---|---|---|---|
| 千葉県千葉市 | apple.txt | 田中 まさし | 資料1_グラフ | 0001_Tanaka-Masahi_Japan |
| 神奈川県横浜市 | cherry.txt | 太田 のぞみ | 資料2_円グラフ | 0002_Ohta-Nozomi_Japan |
| 北海道旭川市 | banana.txt | 武田 ゆうき | 資料3_パワーポイント | 0003_Takeda-Yuki_Japan |
| 栃木県宇都宮市 | orange.txt | 早乙女 ようこ | 資料4_ワード | 0004_Saotome-Yoko_Japan |
ん〜、ありそうでなさそうなデータ群ですね。w
色々試せるように作りましたので、無茶苦茶かもしれませんがこれでやって行きましょう！
1.もらったデータに「.jpeg」のようにいらないものがくっついてるから取り除きたい
人からもらったデータには、あなたが欲しい情報プラス、余計なものがくっついている場合があります。
まずは後半に付いている不要な文字列を取り除く方法をご紹介します。
早速、B10のセルに下記の数式をコピペして見ましょう！！
=LEFT(F4,FIND(“.”,G2))
結果、B2セルの「apple」という情報だけを抜き出せました。
そのままずずずいーっと下にコピペすれば、データ名の一覧から「.txt」を除いた文字列が抜き出せます。
数式の動き：「B2セルの中で「.」（ピリオド）を探して、それより左側の文字列を抜き出す」
LEFT関数を使うと、「指定したセルの左から○つ分の文字列を取り出す」ことができます。
合わせてFIND関数を使うと、こんなことができるわけです。
似たような動きとして、「右から○つ分の文字列を取り出す方法」としてRIGHT関数を使うと、今の動作の右側の情報を取り出すことができます。
試しに、F2のセルに下記の数式をコピペしてください。
=RIGHT(C2,3)
すると、単純に右から何文字抜き出す、というシンプルなアクションができるので、みんなの下の名前を取り出すことができます。
ちなみに、用意したデータはみんな名前が3文字なので綺麗に抜き出せましたが、こちらの数式をF2列に入れれば下の名前が何文字であろうと抜き出せます。
=MID(C2,FIND(“ ”,C2)+1,10)
ちょっと理解するのが難しいかもしれませんが、数式の動きはこのようになっています。
数式の動き：「C3セルの中で空白を探して、その空白の次の文字から10文字抜き出す」
数式の最後の10が10文字という意味なので、もっと長い名前の人がいたら20とか30にすればOKです。（外人さんとか）
2.都道府県と住所が合体したデータから都道府県だけ抜き出したい
今度は、先頭の何文字かを抜き出したいときに使える方法です。
先ほどの表を引き続き使って行きましょう。
では、B10セルに下記の数式をコピペ！
=IF(MID(C2,4,1)=”県”,LEFT(C2,4),LEFT(C2,3))
すると、A列の都道府県名から千葉県が抜き出せると思います。
数式の動き：「A2セルの左から4番目の文字が”県”なら左から4文字抜き出し、そうじゃなければ左から3文字抜き出す」
これで、北海道でも京都府でも抜き出せます。神奈川県、和歌山県、鹿児島県くらいじゃないですか？4文字の県って。
あとはみんな3文字ですから、うまいこと抜き出せるというわけです。
3.「名字 名前」で名字だけ抜き出したい
次は、「名字 名前」のように空白でくっついているデータの中で、前半部分だけを抜き出す方法です。
では、早速B10セルに下記の数式をコピペしてください。
=LEFT(C2,FIND(“ ”,C2))
すると、名字が何文字であろうと、空白の前までの文字列を抜き出すので、橘でも田中でも早乙女でも抜き出せるというわけです。
数式の動き：「B2セルの中で、空白を探してそれより左側の文字列を抜き出す。」
これは私もよく使ったやり方ですので、実践的ではないでしょうか。
4.先頭に毎回不要な文字列があるので、先頭の文字を無視して文字列を取り出したい
先ほどちらっと紹介した下の名前を抜き出す数式を使って、前半の不要な文字列を除いた文字列を抜き出してみましょう。
こちらの数式をB10にコピペしてください。
=MID(D2,FIND(“_”,D2)+1,10)
こちらも先ほどの下の名前の時と似ていて、「_」（アンダーバー）以降の文字を10文字抜き出すということができます。
5.長い文字の中から必要な文字だけを抜き出したい
では最後に、IT業界ではよく使うかな？という方法ですが、前後に不要な文字列が含まれているので、それを取り除いた真ん中の文字列を抜き出す方法です。
作成していただいたデータのE列をご覧ください。
例えばこのような社員データがあって、「この社員データからアルファベットの名前だけ抜き出せたら、ローマ字のリストが作れるぞ」と思ったときに使えます。
これはもう数式がめちゃんこ複雑になるので、理由はさておきコピペでいきましょう！
B10セルにこちらの数式を入れて見てください。
=MID(E2,FIND(“_”,E2)+1,FIND(“_Japan”,E2)-FIND(“_”,E2)-1)
多分うまく名前が抜き出せたと思います。大まかに数式の動きだけ理解しておきましょう。
数式の動き：「最初に見つけた”_”から、”_Japan”までの文字を抜き出す」
MID関数を理解していないと難しいと思いますが、
=MID(A,B,C)
Aは指定するセルを入力、Bは抜き出しを開始する位置、Cは文字数です。（先ほどの画像の数式のA、B、C）
MID関数の中に、FIND関数を入れてアンダーバーやjapanの位置を探しているというわけです。
プラス1とかマイナス1というのは微妙に開始位置などを調整しています。
これを基に中身を変えることで、ほかの長い文字列からも文字列を抜き出せるかと思います。
今回使った関数
LEFT関数・・・左から○文字の文字列を表示させる
RIGHT関数・・・右から○文字の文字列を表示させる
MID関数・・・指定した文字から始めて○文字分の文字列を表示させる
FIND関数・・・検索したい文字列が、何文字目にあたるのか調べられる
次回、全角の情報を半角に、半角の情報を全角にする方法をお伝えいたします。
