管理ソフトから出力したデータを別の管理ソフトに取り込んだり、各種調査で市町村別のリストが必要、というように、エクセルで住所を管理している場合は、そのデータを加工が必要な場合があります。
今回は、エクセルの住所録データの都道府県や市町村を分ける方法とつなげる方法について書いています。
住所録を分ける
1.県の名前だけ取り出す
県だけ取り出すには、LEFT関数とFIND関数を使います。
LEFT関数は、選んだセルの最初から何文字目を指定して取り出す関数です。
LEFT（選んだセル,文字数)
たとえば、A1セルに「山川県谷中市花木町竹台10丁目29番地30号」とあった場合に、「山川県」の3文字だけだけ取り出したいときは、「=LEFT(A1,3)」となります。
住所録の場合、ほとんどの県名は「○○県」で3文字です。
しかし、神奈川県などのように4文字のところもあります。
そのため、数字で指定すると4文字の県がきちんと取り出せないため、「県」の文字できちんと取り出す必要があります。
「県」が何文字目にあるかを数えるためには、FIND関数を使います。
FIND（”数えたい文字”,選んだセル)
A1セルに「山川県谷中市花木町竹台10丁目29番地30号」とあった場合に、「県」が何文字目にあるかを数えるには、「=FIND(“県”,A1)」となります。
この関数で、「3」と数字が出ます。
これは「県」が3文字目にあることを示しています。
上記のLEFTとFINDの関数を組み合わせて、県名を取り出すには、次のようにします。
LEFT(A1,FIND(“県”,A1))
これで、文字数関係なく、県名を取り出すことができます。
(県以外の都道府も同じように取り出せます)
2.県の名前を抜いて取り出す
上記と反対に、県のあとの「谷中市花木町竹台10丁目29番地30号」を取り出す方法です。
A1セルにA1セルに「山川県谷中市花木町竹台10丁目29番地30号」、B1セルに1.で取り出した「山川県」があるとします。
この場合に、次のように入力することで、県のあとの住所が取り出せます。
SUBSTITUTE(A1,B1,””)
SUBSTITUTE関数は、選んだセルの言葉を変換する関数で、「CTRL」＋「H」の置換機能を関数にしたようなものです。
上記だと、「A1セルを選んで、B1のセルの言葉(山川県)を、何もなし(“”)にする」という入力をして、「山川県」を消しています。
これで、「谷中市花木町竹台10丁目29番地30号」を取り出すことができます。
ここから、さらに「谷中市」だけを取り出すには、上記1.の関数の「県」を「市」に変えて取り出します。
3.上記2つを組み合わせて住所を分ける
上記2つで説明した内容で住所を分けると次のようになります。
ブログの文章の都合上、縦に並べていますが、実際には最初のA1セルに元の住所があり、B1セル、C1セル…と横に算式を次々入れていきます。
| セルの場所 | 表示される住所 | 入力する算式 |
| A1セル | 山川県谷中市花木町竹台10丁目29番地30号 | （元のデータ） |
| B1セル | 山川県 | LEFT(A1,FIND(“県”,A1)) |
| C1セル | 谷中市花木町竹台10丁目29番地30号 | SUBSTITUTE(A1,B1,””) |
| D1セル | 谷中市 | LEFT(C1,FIND(“市”,C1)) |
| E1セル | 花木町竹台10丁目29番地30号 | SUBSTITUTE(C1,D1,””) |
| F1セル | 花木町 | LEFT(E1,FIND(“町”,E1)) |
| G1セル | 竹台10丁目29番地30号 | SUBSTITUTE(E1,F1,””) |
このままだと算式になっているため、加工ができません。
コピーして、同じ場所に「値の貼り付け」で算式を消して文字列にします。
そして、必要ではない列(上記だとA,C,Eの各列)を消すことで、県名･市名･町名･残り、のリストを作ることができます。
住所録をつなげる
住所が別々になっているものをつなげる方法は、「＆」を使う方法と、「CONCATENATE関数」を使う方法があります。
いずれも、つなげたいセルを順番に入れていきます。
たとえば、A1セル、B1セル、C1セル、D1セルの４つをつなげたい場合には、次のようになります。
- ＆を使う場合
A1&B1&C1&D1 - CONCATENATE関数を使う場合
CONCATENATE(A1,B1,C1,D1)
どちらの方法でも、県名･市名･町名などのように分かれている住所を1つのセルにまとめることができます。
あとはコピーし、値の貼り付けで文字列として使えるようになります。
まとめ
エクセルの住所の加工についてのまとめです。
- 県や市町村ごとに分ける
LEFT関数とFIND関数、SUBSTITUTE関数を組み合わせる - 住所をまとめる
「＆」またはCONCATENATE関数を使う
