Excelで名簿や住所録、顧客データを扱っていると、文字列の一部分だけを取り出したい場面があります。
たとえばExcelで、メールアドレスから＠以降のドメインだけを取り出したい、住所から都道府県を除いて市区町村以下だけを表示したい、といったケースです。
このような作業を手入力で行うと時間がかかりますが、Excelの関数を使えば一括で処理できます。
この記事では、SUBSTITUTE関数を使って
・住所から市区町村以下を取り出す方法
・メールアドレスから＠以降のドメインを取り出す方法
を順番にお伝えします。
ExcelではSUBSTITUTE関数を使うことで、文字列の不要な部分を削除し、必要な部分だけを取り出せます。
この記事では次の2つをすぐに実行できる形で紹介します。
・メールアドレスの＠以降のドメインを抽出する方法
・住所から市区町村以下を取り出す方法
すぐに数式を見たい方は、このまま下の見出しへ進んでください。
Excelで文字列の一部を取り出す基本の考え方
今回のポイントは、「必要な文字を直接取り出す」のではなく、「不要な文字を消す」という発想です。
この考え方で使うのが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&”@”,””)
「＠」を追加して指定する場合は「&”@”」追記します。
ここでポイントになるのが、「&”@”」の部分です。
ユーザー名だけでなく、「＠」も一緒に削除対象として指定することで、ドメイン部分だけがきれいに残ります。
この方法は、
・会社ドメインごとの集計
・フリーメールか独自ドメインかの判別
など、メールアドレスを分析する場面で特に有効です。
なお、この式は「ユーザー名が別セルに入っている場合」に使いやすい方法です。
メールアドレス1件だけから＠以降を取り出したい場合は、別の文字列関数を使う方がシンプルに処理できることもあります。
関数を調べながら作業していると、毎回同じところで手が止まってしまうことも多いのではないでしょうか。
Excelの文字列関数は、知っているだけで名簿整理や顧客リストの加工が圧倒的に速くなります。
自己流で覚えるよりも、実務で使う関数を体系的に整理しておくと作業効率が大きく変わります。
SUBSTITUTE関数を使う際の注意点
SUBSTITUTE関数は非常に便利ですが、いくつか注意点もあります。
まず、検索文字列が完全一致しないと置き換えが行われません。
全角・半角の違いや、余分なスペースがあると意図した結果にならないことがあります。
また、同じ文字列が複数回含まれている場合、「置換対象」を省略するとすべて置き換えられます。
特定の1回だけを置き換えたい場合は、第4引数を指定する必要があります。
今回のような住所やメールアドレスでは問題になることは少ないですが、応用する際は覚えておくと安心です。
SUBSTITUTE関数で文字列の一部を取り出す方法まとめ
今回は、SUBSTITUTE関数を使って
・住所から市区町村以下を取り出す方法
・メールアドレスの＠以降のドメインを抽出する方法
を紹介しました。
「取り出す」と聞くと難しそうに感じますが、「不要な文字を消す」という考え方に切り替えると、関数は一気にシンプルになります。
この発想は、Excelで文字列を扱う上で非常に重要なので、ぜひ覚えておいてください。
住所録や顧客データ、メール一覧の整理など、実務でもすぐに使えるテクニックです。
ぜひご自身のデータで試しながら、SUBSTITUTE関数に慣れていきましょう。
コメント