【永久保存版】Excelで都道府県、住所、番地、その他を抽出する

いつもいつも悩まされる都道府県、市区町村、番地、その他を抽出する関数を。

前提：
・1行目にはカラム名
・2行目から左から、全住所、都道府県、市区町村、番地+その他、番地、その他

1. 都道府県

=IF(MID(A2,4,1)="県",LEFT(A2,4),LEFT(A2,3))

1. 市区町村（政令指定都市対応）

=IF(
 AND(COUNTIF(A2,"*都*"),COUNTIF(A2,"*区*")),
 MID(A2,4,FIND("区",A2,1)-FIND("都",A2,1)),
  IF(
   AND(COUNTIF(A2,"*都*"),COUNTIF(A2,"*市*")),
   MID(A2,4,FIND("市",A2,1)-FIND("都",A2,1)),
    IF(
     AND(COUNTIF(A2,"*府*"),COUNTIF(A2,"*区*")),
     MID(A2,4,FIND("区",A2,1)-FIND("府",A2,1)),
       IF(
        AND(COUNTIF(A2,"*府*"),COUNTIF(A2,"*市*")),
        MID(A2,4,FIND("市",A2,1)-FIND("府",A2,1)),
          IF(
           AND(COUNTIF(A2,"*県*"),COUNTIF(A2,"*区*")),
           MID(A2,FIND("県",A2,1)+1,FIND("区",A2,1)-FIND("県",A2,1)),
             IF(
              AND(COUNTIF(A2,"*県*"),COUNTIF(A2,"*市*")),
              MID(A2,FIND("県",A2,1)+1,FIND("市",A2,1)-FIND("県",A2,1)),
                IF(
                 AND(COUNTIF(A2,"*道*"),COUNTIF(A2,"*区*")),
                 MID(A2,FIND("道",A2,1)+1,FIND("区",A2,1)-FIND("道",A2,1)),
                   IF(
                    AND(COUNTIF(A2,"*道*"),COUNTIF(A2,"*市*")),
                    MID(A2,FIND("道",A2,1)+1,FIND("市",A2,1)-FIND("道",A2,1)),
                     IF(
                      AND(COUNTIF(A2,"*道*"),COUNTIF(A2,"*郡*")),
                      MID(A2,FIND("道",A2,1)+1,FIND("郡",A2,1)-FIND("道",A2,1)),
                     )
                   )
                 )
              )
           )
        )
     )
  )
)

3. 番地+その他

=SUBSTITUTE(A2,LEFT(A2,FIND( RIGHT(C2,1),A2,1)),"")

4. 番地

=IF(ISERROR(LEFT(D2,FIND(" ",D2)))=TRUE, D2,LEFT(D2,FIND(" ",D2)))

5. その他

=IF(E2="", "",IF(ISERROR(SUBSTITUTE(D2,E2,""))=TRUE,"",SUBSTITUTE(D2,E2,"")))