「Excel 都道府県名を抽出」記事では、住所録の住所列に記載される住所から都道府県名を取り出す方法をご紹介しました。
これは、住所に必ず都道府県名が含まれている場合に適用できます。
ところが、住所録の中には都道府県名がない住所が出てくることもありますね。
そのような住所について都道府県名を知る方法について考えてみましょう。
ここでも、「Excel 郵便番号から市区町村名を得る」記事で使った郵便番号データを使ってみます。
郵便局が提供している「郵便番号データダウンロード」ページにアクセスし、「住所の郵便番号（CSV形式）」の「読み仮名データの促音・拗音を小書きで表記するもの」をクリックします。
（今回は、読み仮名データは使いませんので、「読み仮名データの促音・拗音を小書きで表記しないもの」でもよいし、その下の「住所の郵便番号（ローマ字）」でもよいです。）
「読み仮名データの促音・拗音を小書きで表記するもの(zip形式)」ページが開いたら、「都道府県一覧」項の所要の都道府県あるいは「全国一括」をクリックしてダウンロードします。
「全国一括」の場合は「ken_all.zip」がダウンロードされます。
これを解凍すると「KEN_ALL.CSV」というファイルが取り出せますので、これを Excelで開きます。（下図）
これを Excel形式にするため、右上にある「名前を付けて保存」ボタンを押し、例えば「郵便番号.xlsx」と名前を付けて保存します。
各列のデータは「郵便番号データの説明」ページをご参照ください。
使いやすくするため、項目行を加え、不要な列を削除します。
なお、「郵便番号」列のデータは、[セルの表示形式]-[その他]-[郵便番号]で見やすくしてみました。（下図）
これが検索用の郵便番号-市区町村対応表になります。
それでは、下図のような住所録を例に都道府県名を取り出してみます。
この住所録の中で「住所1」列には都道府県名が入力されていない住所も含まれています。
「Excel 都道府県名を抽出」記事のように、セルE1に
=IF(MID(F2,4,1)="県",LEFT(F2,4),LEFT(F2,3))
と入力し、下方にオートフィルします。（下図）
一見うまく取り出せているようですが、上図の表中、赤枠で囲った住所については、単に最初の 3文字を取出しているだけで、都道府県名になっていません。
そのため、もうひと工夫が必要です。
(1) 取り出した文字列の最後が「都道府県」でない場合
手順を分かりやすくするため、郵便番号-市区町村対応表を住所録と同じブックに「郵便番号」シートとしてコピーし、さらに途中経過を一時保存するため、H～J列を使用します。
まず、セルH2に上記同様に
=IF(MID(F2,4,1)="県",LEFT(F2,4),LEFT(F2,3))
と入力し、下方にオートフィルします。
この H列のデータに都道府県名以外の市区町村名が入っているところを、最初の 3文字だけ拾い出します。
セルI2に
=IF(OR(RIGHT(H2)="都",RIGHT(H2)="道",RIGHT(H2)="府",RIGHT(H2)="県"),"",LEFT(H2,3))
と入力し、下方にオートフィルします。
そして、I列に市区町村名の先頭の 3文字が表示されている行について、例えばセルJ5に
=INDEX(郵便番号!$A$2:$D$124666,MATCH(I5&"*",郵便番号!$C$2:$C$124666,0),2)
と入力します。（下図）
これらの数式をまとめてセルE2に
=IF(OR(RIGHT(H2)="都",RIGHT(H2)="道",RIGHT(H2)="府",RIGHT(H2)="県"),H2,INDEX(郵便番号!$A$2:$D$124666,MATCH(I5&"*",郵便番号!$C$2:$C$124666,0),2))
と入力し、下方にオートフィルします。（下図）
数式中の「H2」を
IF(MID(F2,4,1)="県",LEFT(F2,4),LEFT(F2,3))
に置き換えるなどしてもよいですが、数式が読みづらくなるだけなので、今回は H～J列を非表示とすることにします。
また、同じブック内に「郵便番号」シートを追加して行いましたが、別のブックにあっても参照出来ます。
ただし、それぞれを置くフォルダの絶対パスは変えないでください。
(2) 単に郵便番号で検索する
今回使用した「住所録」シートと「郵便番号」シートの表データには両方とも「郵便番号」列があります。
これらが利用できるときは、単に VLOOKUP関数などで検索すればよいですね。
セルE2に
=VLOOKUP(D2,郵便番号!A2:$D$124666,2,FALSE)
と入力し、下方にオートフィルします。
結果は、もちろん上図と同じになります。
さて、上記(1)のように幾重にも関数がネスト（入れ子）になるようなときは、演算の途中結果を一時的に保存するセルを設けるとよろしいと思います。
あるいは、関数を定義する LAMBDA関数を使ったり、マクロ(VBA)を使ったりするという手もあるでしょう。