住所録や顧客リストを扱っていると、「東京都新宿区西新宿」のように1つのセルへ入力された住所から、都道府県だけを取り出したい場面がありますよね。手作業で分けると件数が多いほどミスが増えるため、Excelの関数で自動分割するのが早くて安心です。私が実務で使いやすい方法を、住所がA列に入っているケースで解説します。
結論：都・道・府・県の位置を探して都道府県を抜き出します
Excel 365やExcel 2021など、配列数式をそのまま入力できるExcelなら、次の式で住所から都道府県を取り出せます。住所がA2セルにある場合は、都道府県を表示したいB2セルへ入力してください。
この関数は、住所内にある「都」「道」「府」「県」のうち、最初に見つかった文字までを抜き出します。たとえば「東京都新宿区西新宿」は「東京都」、「北海道札幌市中央区」は「北海道」、「大阪府大阪市北区」は「大阪府」と表示されます。
住所から都道府県を分割する手順
ここでは、A列に元の住所、B列に都道府県、C列に都道府県を除いた残りの住所を表示する例で進めます。1件だけではなく、何百件の住所にも同じ手順で対応できますよ。
- A1セルに「住所」、B1セルに「都道府県」、C1セルに「市区町村以下」など、分かりやすい見出しを入力します。
- A2セルに「東京都新宿区西新宿2-8-1」のような住所を入力、または貼り付けます。
- B2セルをクリックします。
=LEFT(A2,MIN(IFERROR(FIND({"都","道","府","県"},A2),LEN(A2)+1)))を入力します。- Enterキーを押します。B2セルに「東京都」と表示されたら成功です。
- C2セルをクリックし、
=REPLACE(A2,1,LEN(B2),"")と入力します。 - Enterキーを押すと、「新宿区西新宿2-8-1」のように都道府県を除いた住所が表示されます。
- B2とC2の右下にある小さな四角形（フィルハンドル）をダブルクリックします。A列にデータが続いている最終行まで、数式をまとめてコピーできます。
C列の式は、A2セルの先頭からB2セルの文字数分だけを削除する仕組みです。そのため、東京都・北海道・京都府・神奈川県など、都道府県名の長さが違っても問題ありません。
データを確認しやすいサンプル
- A2：東京都新宿区西新宿2-8-1 → B2：東京都 ／ C2：新宿区西新宿2-8-1
- A3：北海道札幌市中央区北一条西2丁目 → B3：北海道 ／ C3：札幌市中央区北一条西2丁目
- A4：京都府京都市中京区烏丸通 → B4：京都府 ／ C4：京都市中京区烏丸通
- A5：福岡県福岡市博多区博多駅前 → B5：福岡県 ／ C5：福岡市博多区博多駅前
古いExcelで配列数式として入力する場合
Excel 2019以前などの環境では、上の都道府県抽出式を入力しただけでは正常に計算されないことがあります。その場合は、B2セルに数式を入力したあと、Enterキーだけで確定せずにCtrl＋Shift＋Enterを押してください。数式バーに波かっこが付くことがありますが、自分で波かっこを入力する必要はありません。
なお、職場のExcelのバージョンが分からない場合は、まずEnterキーで試しましょう。正しく「東京都」などが表示されれば、そのままで大丈夫です。
関数を使わずに分割するならフラッシュフィルも便利です
住所の表記が比較的そろっていて、一度だけ分割したいときはフラッシュフィルも時短になります。数式を残さず、結果だけを素早く作りたい場合に向いています。
- B2セルに、A2の住所から取り出した都道府県を手入力します。たとえばA2が「東京都新宿区西新宿」なら、B2には「東京都」と入力します。
- B3セルをクリックします。
- Ctrl＋Eを押します。
- Excelが住所の規則を推測し、B列へ都道府県を自動入力します。
フラッシュフィルは便利ですが、住所の先頭に郵便番号が付いていたり、表記ゆれが多かったりすると誤った結果になることがあります。継続的に使う住所録なら、再計算できる関数で分ける方法がおすすめです。
上手くいかない場合のチェックポイント
都道府県名ではなく住所全体が表示される
住所に「都」「道」「府」「県」が含まれていない可能性があります。たとえば「新宿区西新宿」のように都道府県を省略したデータでは、関数は分割できません。元データに都道府県が入っているか確認しましょう。
郵便番号や〒マークが住所の先頭にある
「〒160-0023 東京都新宿区」のようなデータでも都道府県は抽出できますが、C列には郵便番号を含んだ文字列が残ります。郵便番号も別列に分けたい場合は、先に郵便番号を削除するか、区切り位置機能で分けてから住所の分割を行うと整理しやすいですよ。
数式がそのまま文字として表示される
セルに数式ではなく文字列として表示される場合は、セルの表示形式が「文字列」になっていることがあります。対象セルを選択し、［ホーム］タブの［表示形式］から［標準］へ変更してください。その後、F2キーを押してからEnterキーで数式を再確定します。
「#VALUE!」エラーになる
コピー元にエラー値が入っている、または古いExcelで配列数式として確定できていないことが主な原因です。A2セルの内容を確認し、古いExcelではCtrl＋Shift＋Enterで入力し直しましょう。また、全角・半角スペースだけのセルや空白セルが混ざっていないかも確認してください。
「京都府京都市」のように都と府が両方ある住所が心配
この関数は住所の中で最初に出てくる「都・道・府・県」の位置を使います。「京都府京都市」であれば最初の「府」までを取り出すため、結果は正しく「京都府」になります。東京都、大阪府、北海道、各県の住所にまとめて使えます。
仕上げに覚えたい住所データの時短ワザ
関数で分割した結果を取引先へ渡すなど、数式ではなく文字として固定したいときは「値の貼り付け」を使いましょう。B列とC列を選択してCtrl＋Cを押し、同じ場所で右クリックして［値の貼り付け］を選べば、数式だけを結果の文字列へ置き換えられます。
- Ctrl＋C：コピー
- Ctrl＋V：貼り付け
- Ctrl＋E：フラッシュフィル
- Ctrl＋Shift＋L：フィルターのオン・オフ
- Ctrl＋1：セルの書式設定を開く
住所を都道府県ごとに確認したいときは、見出し行を含めて表を選び、Ctrl＋Shift＋Lでフィルターを付けると便利です。B列の▼から「東京都」だけを選べば、エリア別の件数確認や担当振り分けもスムーズになります。まずは関数で都道府県を分割し、必要に応じてフィルターや並べ替えを組み合わせてみましょう。
