Excelで名簿や住所録、顧客データを扱っていると、「文字列の一部分だけを取り出したい」という場面は意外と多いものです。
たとえば、メールアドレスから「＠以下のドメイン部分だけを抽出したい」、あるいは住所録から「都道府県を除いた市区町村以下だけを取得したい」といったケースです。
1件ずつコピー＆ペーストすれば対応できなくもありませんが、件数が増えると現実的ではありません。
そこで活躍するのが、Excelの文字列関数です。関数を使えば、同じ処理を大量のデータに一括で適用でき、作業時間を大幅に短縮できます。
この記事では、比較的シンプルで汎用性の高い「SUBSTITUTE関数」を使って、
・住所から都道府県部分を取り除く方法
・メールアドレスから＠以下のドメインを取り出す方法
をわかりやすく解説します。
関数に慣れていない初心者の方でも理解できるよう、考え方から順を追って説明していきます。
指定した文字範囲を空白に置き換える
今回のポイントは、「必要な文字を直接取り出す」のではなく、「不要な文字を消す」という発想です。
この考え方で使うのがSUBSTITUTE関数です。
SUBSTITUTE関数の基本構文は次のとおりです。
SUBSTITUTE（文字列, 検索文字列, 置換文字列, [置換対象]）
この関数は、「文字列」の中に含まれる「検索文字列」を探し、それを「置換文字列」に置き換えます。
「置換対象」は省略可能で、省略した場合は該当する検索文字列すべてが置き換えられます。
つまり、
・消したい文字列を「検索文字列」に指定
・「置換文字列」を空白（””）にする
ことで、特定の文字だけを削除することが可能になります。
この仕組みを理解しておくと、住所やメールアドレス以外にも、商品コードやID、文章データの加工など、幅広い場面で応用できます。
SUBSTITUTE関数で特定の文字列を削除する考え方
SUBSTITUTE関数は、「抽出」というよりも「置き換え」が本質です。
そのため、「残したい文字」ではなく「消したい文字」に注目すると、式がシンプルになります。
今回の例で言えば、
・住所の場合は「都道府県名」を消す
・メールアドレスの場合は「ユーザー名＋＠」を消す
という発想になります。
この考え方を頭に入れたうえで、具体例を見ていきましょう。
都道府県以下の住所を取り出す方法
住所録でよくあるのが、
「東京都新宿区〇〇町1-2-3」
「大阪府大阪市北区〇〇町…」
といった形式のデータです。
ここから「新宿区〇〇町1-2-3」や「大阪市北区〇〇町…」だけを取り出したい場合、
都道府県名を削除すれば目的を達成できます。
サンプルのように
B3セル：フル住所
C3セル：都道府県名
が入力されている場合、次の式を使います。
=SUBSTITUTE(B3,C3,””)
B3セル文字列のうちC3セルの文字列を「””」空白にし取り出します。
この式は、B3セルの文字列の中から、C3セルに入力されている都道府県名を探し、それを空白に置き換えます。
結果として、都道府県名だけが消え、市区町村以下の住所が残ります。
都道府県名を別セルに用意しておくことで、全国どの住所にも柔軟に対応できるのがメリットです。
名簿データを整理したり、エリア別に集計したりする際に非常に役立ちます。
メールアドレスからドメインを取り出す方法
次に、メールアドレスから＠以下のドメイン部分を取得する方法です。
たとえば、
example.user@gmail.com
というアドレスから、
gmail.com
だけを取り出したい場合を考えます。
この場合に消したいのは、「ユーザー名＋＠」の部分です。
A3セルにメールアドレス、B3セルにユーザー名が入っていると仮定すると、次の式を使います。
=SUBSTITUTE(A3,B3&”@”,””)
「＠」を追加して指定する場合は「&”@”」追記します。
ここでポイントになるのが、「&”@”」の部分です。
ユーザー名だけでなく、「＠」も一緒に削除対象として指定することで、ドメイン部分だけがきれいに残ります。
この方法は、
・会社ドメインごとの集計
・フリーメールか独自ドメインかの判別
など、メールアドレスを分析する場面で特に有効です。
SUBSTITUTE関数を使う際の注意点
SUBSTITUTE関数は非常に便利ですが、いくつか注意点もあります。
まず、検索文字列が完全一致しないと置き換えが行われません。
全角・半角の違いや、余分なスペースがあると意図した結果にならないことがあります。
また、同じ文字列が複数回含まれている場合、「置換対象」を省略するとすべて置き換えられます。
特定の1回だけを置き換えたい場合は、第4引数を指定する必要があります。
今回のような住所やメールアドレスでは問題になることは少ないですが、応用する際は覚えておくと安心です。
おわりに
今回は、SUBSTITUTE関数を使って、
・住所から都道府県名を除外し、市区町村以下を取り出す方法
・メールアドレスから＠以下のドメインを取得する方法
を解説しました。
「取り出す」と聞くと難しそうに感じますが、「不要な文字を消す」という考え方に切り替えると、関数は一気にシンプルになります。
この発想は、Excelで文字列を扱う上で非常に重要なので、ぜひ覚えておいてください。
住所録や顧客データ、メール一覧の整理など、実務でもすぐに使えるテクニックです。
ぜひご自身のデータで試しながら、SUBSTITUTE関数に慣れていきましょう。
コメント