「コピぺ de 関数」FILE_04
今回ご紹介する方法は下記のようなときに使える方法です。
- 電話番号のリストの中から「-」を取り除きたい！
- 郵便番号のリストの中から「-」を取り除きたい！
- 「2017」を「2018」に置き換えたい
- 不要な余白を消したい
- とにかくリスト内の指定した文字を、別の文字列に置き換えたい
- セル内の○文字目から○文字分の文字列を、別の文字列に置き換えたい
基本的にはsubstitute関数を使って行きますが、上記の最後だけはreplace関数を使って行きます。
とにかく”置き換え”を実践してみよう
難しい理屈は後で！が「コピペ de 関数」ですので、まずは自身の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 |
指定した文字列を、別の文字列に置き換える方法
まずは、作成いただいた表の中で、B列の情報を置き換えてみましょう。
B列の「.txt」という文字を「.jpeg」に変えてみたいと思います。
下記の数式をF2セルにコピペしてみてください。
=SUBSTITUTE(B2,”.txt”,”.jpeg”)
すると、apple.jpegという文字列に変わったと思います。
そのまま、F2セルを下にずずずいーっとコピペすれば、B列で「.txt」が含まれる場合、「.txt」が「.jpeg」に置き換えられます。
数式の動きを説明すると、「B2セルに.txtが含まれていたら、.jpegに置き換える」ということになります。
数式内の「”」は文字列を意味する上で必要ですので、指定する文字列を必ず「”」で挟んであげます。「”.txt”」のように。
ちなみに、B5セルには「.txt」が含まれておりませんので、F2にコピペした数式を下にコピーしていくと、F5セルは特に何も変わることなく「orange.xls」のままとなります。
エラーもでませんので使いやすいですね！
応用することで文字間を詰めることが可能！
では、先ほどのやり方を応用して、指定した文字を空白に置き換える（＝文字間を詰める）方法を解説します。
F2セルに下記の数式をコピペしてみてください。
=SUBSTITUTE(D2,”_”,””)
すると、「資料1グラフ」となります。
前半の””内に消したい文字列を入れて、後半に””だけを入れれば空白に置き換えられます。
無駄な情報が挟まっているときに便利ですね。
指定した文字がセル内に2つ以上入ってるときは？
指定した文字の置き換えはできましたが、もし、セル内に指定した文字が2つ以上入っていたらさらに設定が必要です。
E列をご覧ください。
E列は「_」が2つ含まれていますよね？
そのうち、後ろの「_」だけを別の文字に置き換えることができます。
それには、下記の数式をB10にコピペしてみてください。
=SUBSTITUTE(E2,”_”,””,2)
すると、E2セルの0001の後の「_」は変わらず、後半の「_」だけ置き換わります。
数式の中身を紐解いていくと、このようになっています。
引数の順番として、セルの指定、文字列、文字列と入れた最後「2」という文字が入っています。
これは省略できるので最初に説明した数式には入っていませんでしたが、入力することで「何番目に出てきたものか」を指定できます。
これでいろんな文字列を置き換えることができるようになります。
指定した文字ではなく、先頭から○文字目の文字を置き換える
これまで説明した方法がよく使われますが、単純に「先頭から○番目の文字から始め、そこから○文字分を別の文字に置き換える。」こともできます。
試しに、F2セルに下記の数式をコピペしてみてください。
=REPLACE(D2,4,10,””)
コピペしてさらに下にコピペすると、下記の画像のように資料1〜4となります。
数式の動きとしては「D2セルの中で、4番目の文字から10文字分を空白にする」という動きになります。
例では3つ目の引数に「10」を入れていますが、適当に10文字分ということにしています。
10じゃなくてもセルに入ってそうな置き換えたい文字数を入れれば大丈夫です。（いっそ、100とか）
ぜひ色々置き換えをしてみて、自分が利用しやすいデータとなるよう修正してみてください。
今回使った関数
SUBSTITUTE関数・・・指定した文字列を別の文字列に置き換える
REPLACE関数・・・先頭から何文字目の文字から、○文字分の文字列を別の文字列に置き換える
次回は、余分なスペースをまとめて削除する（TRIM関数）方法を解説したいと思います。
