こんにちは。ホームスタッフサービスのK田です。
弊社では普段仕事でExcelを使っております。ブログ「Excelの機能を紹介するシリーズ」で第一弾は「フィルター機能を使いこなそう」をしました。今回の第二弾は「関数」です。関数だと範囲が広いので、今回はあまり解説されることのない「文字列整形」に絞ります。
私は文字列整形で「関数」をよく使います
私は普段、仕事でExcelを使って、文字列整形をたくさん行っています。例えば、某公式HPから取得した「名称」「住所」「電話番号」をExcel表へコピー&ペーストするなどしています。しかし、そのままですと半角と全角が混ざっていたり、不要な改行やスペースを含んでいたりすることがあります。このような場合、私は「関数」を使って整形しています。
今回やること
では実際に関数を使った文字列整形をやってみましょう。整形のサンプルとして「愛媛県内の市区町村役場一覧表」を作成してみました。こんな感じです。
今回は赤枠の「住所」欄の文字列整形を行います。課題は以下の通りです。
- 半角を全角に修正する
- スペースを除去する
- 郵便番号部分のみ抜き出す
- 住所部分のみ抜き出す
以上の4つです。
1.半角を全角に変換する
使用する関数は「JIS関数」です。半角文字を全角文字に変換する関数です。
＝JIS(文字列)
E列に変換結果が入るようにします。セルE2へ「=JIS(D2)」と入力します。
関数の結果がセルE2で表示されました。
セルE2をセルD2へ「値として貼り付け」をします。右クリックをすると、青枠内の「貼り付けのオプション」が表示されますので、こちらを使用します。
貼り付けのオプションの赤枠のアイコン（値として張り付け）を選択します。
セルD2にて「半角を全角に変換した結果」を反映することができました。
2.スペースを除去する
使用する関数は「SUBSTITUTE関数」です。文字列の中の指定した箇所を置換する関数です。少しややこしい関数ですが、使いこなせると非常に便利です。
＝SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象) ※置換対象は省略できます
E列に変換結果が入るようにします。セルE2へ「=SUBSTITUTE(D2,” ”,””)」と入力します。
関数の結果がセルE2で表示されました。
セルE2をセルD2へ「値として貼り付け」すれば、セルD2にて「スペースを除去する」ができました。（※「値として貼り付け」の操作はこちらを参照。）
3.郵便番号のみ抜き出す
住所文字列の郵便番号の部分のみ抜き出したい場合、どのようにすれば良いでしょうか。
郵便番号部分は「左から9文字（郵便マークとハイフン含む）」と一定ですので、「文字列の左から9文字取得する」とすれば、郵便番号部分のみ抜き出せそうです。
使用する関数は「LEFT関数」です。文字列の左から指定した文字数を取り出す関数です。
＝LEFT(文字列,文字数)
E列に変換結果が入るようにします。セルE2へ「＝LEFT(D2,9)」と入力します。
関数の結果がセルE2で表示されました。
セルE2をセルC2へ「値として貼り付け」すれば、セルC2にて「郵便番号のみ抜き出す」ができました。（※「値として貼り付け」の操作はこちらを参照。）
4.住所部分のみ抜き出す
住所文字列の住所部分のみ抜き出す場合、どのようにすれば良いでしょうか。
手順は以下の通りです。
- 「全体の文字数」を取得する
- 「文字列の右端」から指定した文字数を取り出す
- 「計算結果」をもとに住所部分を抜き出す
「全体の文字数」を取得する
使用する関数は「LEN関数」です。
LEN関数は文字数を返す関数です。
＝LEN(文字列)
E列に変換結果が入るようにします。セルE2へ「=LEN(D2)」と入力します。
関数の結果がセルE2で表示されました。
セルD2の文字数は「25」と分かりました。
「文字列の右端」から指定した文字数を取り出す
使用する関数は「RIGHT関数」です。
RIGHT関数は文字列の右から指定した文字数を取り出す関数です。
＝RIGHT(文字列,文字数)
住所「〒７９４－８５１１愛媛県今治市別宮町１丁目４番地１」の郵便番号を取り除いた住所部分は、右から数えて「16」となります。RIGHT関数を使うと、次の関数式で表すことができます。
E列に変換結果が入るようにします。セルE2へ「=RIGHT(D2,16)」と入力します。
関数の結果がセルE2で表示されました。
住所部分のみ取り出しが出来ました。このRIGHT関数を使って必要な部分を取り出していきましょう。
計算結果をもとに住所部分を抜き出す
全ての住所が同じ文字数なら良いのですが、下記の通り住所の文字数はそれぞれ変化しますので、そのたびに関数式に「何文字目」と指定するのは大変です。
そこで「全体の文字数」から「左から9文字」を引くを行います。
仕組みは以下のイメージです。
住所「〒７９４－８５１１愛媛県今治市別宮町１丁目４番地１」の文字列数はLEN関数で前述しました通り「25文字」です。郵便番号の文字数は「左から9文字」です。RIGHT関数は右から指定された文字数を取得しますので、「右から16文字分が住所部分」となります。この計算式を関数式にあてはめますと
「=RIGHT(D2,LEN(D2)-9)」
となります。
E列に変換結果が入るようにします。セルE2へ「=RIGHT(D2,LEN(D2)-9)」と入力します。
関数の結果がセルE2で表示されました。
セルE2をセルD2へ「値として貼り付け」すれば、セルD2にて「住所部分のみ抜き出す」ことができました。（※「値として貼り付け」の操作はこちらを参照。）
また、セルE2の関数式をセルE21までコピーしますと、住所文字数が変わっても「住所部分のみ抜き出す」ことができました。
最後に、セルE列をセルD列に「値として貼り付け」すれば、今回の課題は完了です。完成図が以下となります。（※「値として貼り付け」の操作はこちらを参照。）
まとめ
今回は関数を使って、住所欄の文字列整形を行いました。一括で処理をするデータ量が多ければ多いほど関数の効果はさらに発揮できそうです。
私の失敗談なのですが、文字列整形のデータ量が少ない時、関数を使わずに手動修正をしていたところ次のようなミスをよくしてしまいました。
- 目視では見えないスペースが残っていた
- 全角だと思ったら、実は半角だった
- 手入力した際に誤字を入力してしまった
関数を使うことで、目視では見つけにくいミスをが回避することができるかもしれません。
ここまでご覧いただきありがとうございました。