文字列操作の例(住所を都道府県とそれ以外に分ける)

都道府県を含んだ住所から「都道府県だけ」と「都道府県を除いた住所」を分ける例です。
システムの移行などにあるケースでしょうか．．．   単なる「住所」から「都道府県」だけを別項目に分離させるというのは、「都道府県」は何かの検索や分類等で利用するなどが考えられますが、 顧客や従業員などのデータベースシステムのリプレースが発生した時など、不定期な用途ではよくあることだと思います。



 以下、処理例を３つ挙げましたが、まともなのは「処理例③」です。
[処理例①]「都」「道」「府」「県」の文字を探して、その位置で分割させてみます。



 A列にある都道府県付きの住所から、「都道府県だけ」をB列に、「都道府県を除いた住所」をC列に分割されるサンプルです。
  • 実際に分割する前に「都道府県」が何文字目にあるかを別の列に入れた式で中間計算させます。
    住所から都道府県を分離
    (画像をクリックすると、このページのサンプルがダウンロードできます)
    この中間計算はD列からG列でそれぞれ「都(D)」「道(E)」「府(F)」「県(G)」をSEARCH関数で算出させています。
  • 次に都道府県どれかで文字位置が判定できた値をH列にセットしています。これはISERR関数でD列から順にエラーでない列の値をセットさせています。
  • そして、都道府県の位置が判明したら、その左端から文字位置までをB列に(LEFT関数)、 その次の文字位置から右をC列(MID関数)に転記させています。
  • 「都道府県」がない場合(11行目)は、H列のエラー判定でD列からG列全てがエラーになると、H列の値は「0」になるようにしており、この場合はB列、C列の計算式で対応できています。
  • 逆に「都道府県」で2種類が見つかってしまう例が9行目です。「京都府」は「府」が見つかる前に「都」も見つかってしまいます。「都道府県」の並べる順番にも工夫が必要です。
    また、この例にはありませんが、「都道府県」がなく、しかも以降の住所に「都道府県」のどれかの文字が含まれてしまう場合も同様に不具合が生じることになります。

[処理例②]ならば、これではどうでしょう。



 「都道府」については、4つのみなので、直接「1都1道2府」を確認し、該当なければ「県」を探すことにしました。
住所から都道府県を分離
「1都1道2府」は全て3字なので、先に左3字をD列に用意しておいてE列で判断します。「県」までも見つからない場合は「0」ではなく「99」とし、B列やC列での「都道府県」発見の判断を4文字以内に変更しました。
これでも「県」の記載がなく、市名に「県」の文字でもあれば不具合が出ますが．．．

[処理例③]「都道府県」リストを探索する方法



 これなら、不安要素は除外できると思います。
都道府県は現在は47種類しかないのですから、47種類をリストにしてサーチすることにします。

 住所から都道府県を分離



 まず、別シートに「都道府県リスト」を作成します。画面サンプルでは全部が見えませんが「沖縄県」までの全都道府県が並んでいます。
都道府県は3文字の場合と4文字の場合があるので、これをA列とB列に分けました。
住所の前方の都道府県部分を「都道府県リスト」から「VLOOKUP関数」で探し出しますが、「3文字で探して、見つからなかったら4文字で探す」という 処理になるので、3文字のリストと4文字のリストを分けておいた方が無駄な参照がないと考えた方法です。
「見つからなかったら」という判定は「IFERROR関数」で行ないます。

 住所から都道府県を分離



 このB列(都道府県)の式を改行を入れて、各関数の引数の桁位置を揃えると、このようになります。

=IFERROR(VLOOKUP(LEFT($A2,3)
                ,都道府県!$A$1:$A$44
                ,1
                ,FALSE
                )
        ,IFERROR(VLOOKUP(LEFT($A2,4)
                        ,都道府県!$B$1:$B$3
                        ,1
                        ,FALSE
                        )
                ,""
                )
        )



先にA列の住所から左3文字を取り出してVLOOKUP関数で「都道府県リスト」を探索します。 見つかれば見つかった都道府県を返します。
VLOOKUP関数には上にIFERROR関数をかぶせてあるので、見つからなければ第2引数の動作となり、 今度は2番目のIFERROR関数の下で左4文字を取り出してVLOOKUP関数で「都道府県リスト」を探索します。
これでも見つからなければ空文字列を返すというものです。
この方法なら「作業列」も使用しないで実現できます。



 なお、Excel2013以降の環境であれば「IFERROR関数」ではなく「IFNA関数」をお勧めします。
使い方は上記の式の「IFERROR」を「IFNA」に置き換えるだけです。（サンプルは提示していません）
IFERROR関数では「#N/A」以外のエラーも拾ってしまいますが、 IFNA関数なら「#N/A」のみで分岐されるので、このような処理では間違いが起きないメリットがあります。
最初から「IFNA関数」をお勧めできなかったのは、Microsoft Officeのサポート期間の問題によります。
社内利用などでは運用環境のOfficeのバージョンの利用状況をご確認下さい。



 また、「都道府県」シートは改廃は発生しないと思われるので、この処理ブックを再利用のために保存しておく場合は非表示にしておいても問題は無いと思います。