エクセルの数式で教えてください。

Excelの数式で教えてください。
セルAR6に設定する数式を教えてください。
セルCE5に「北海道内の各住所」が表示されます。
北海道内の各市町村のリストがセルCU3～CU189にあります。
例えばCE5に「北海道札幌市豊平区～」と表示されたらAR6には「札幌市」と表示
同じくCE5に「北海道旭川市神楽町～」と表示されたらAR6に同じく「旭川市」と表示
同じくCE5に「北海道紋別郡遠軽町～」と表示されたらAR6に同じく「遠軽町」と表示
同じくCE5に「北海道紋別郡西興部村～」と表示されたらAR6に同じく「西興部村」と表示
のように道内の179市町村をCE5のキーワードでAR6に表示出来る方法を教えてください。
上記の場合分けに追加して、特殊な市町村があります。
例えばCE5に「北海道川上郡弟子屈町川湯～」と表示されたらAR6には「弟子屈町（川湯地区）」と表示
同じくCE5に「北海道勇払郡安平町追分～」と表示されたらAR6には「安平町(追分)」
と表示
同じくCE5に「北海道勇払郡むかわ町～」と表示されたらAR6には「勇払郡むかわ町」
と表示
同じくCE5に「北海道勇払郡むかわ町穂別～」と表示されたらAR6には「むかわ町（旧穂別）」
と表示
同じくCE5に「北海道二海郡八雲町熊石～」と表示されたらAR6には「八雲町（旧熊石町）」
と表示
同じくCE5に「北海道紋別郡湧別町～」と表示されたらAR6には「湧別町（旧湧別町）」
と表示
同じくCE5に「北海道紋別郡湧別町上湧別～」と表示されたらAR6には「湧別町（旧上湧別町））」
と表示の全部で7つが特殊な場合です。
最初の場合分けと追加の特殊な場合分けを全て出来る数式を教えてください。
よろしくお願いいたします。

No.2 ベストアンサー 回答者： goomania 回答日時： 2023/06/07 00:11

>北海道内の各市町村のリストがセルCU3～CU189にあります。

とのことですが、北海道の市町村は179しかありません。

(1)「北海道川上郡弟子屈町川湯～」⇒「弟子屈町（川湯地区）」
(2)「北海道勇払郡安平町追分～」⇒「安平町(追分)」
(3)「北海道勇払郡むかわ町～」⇒「勇払郡むかわ町」
(4)「北海道勇払郡むかわ町穂別～」⇒「むかわ町（旧穂別）」
(5)「北海道二海郡八雲町熊石～」⇒「八雲町（旧熊石町）」
(6)「北海道紋別郡湧別町～」⇒「湧別町（旧湧別町）」
(7)「北海道紋別郡湧別町上湧別～」⇒「湧別町（旧上湧別町））」
と表示の全部で7つが特殊な場合です。

という追加説明がありますが、上記の特殊対応で、もともとの住所の一覧を2つに分けるために１行追加する必要があるのは(1)(2)(4)(5)(7)の5つだけです。(3)(6)はもともとの一覧の表示用の地名を変えるだけで済むからです。つまり179+5=184の分類があれば足りるので、CU3～CU189には187のセルがありますが、3つ余ります。とりあえずCU3～CU186を使うことにします。

住所表記の文字列の中に含まれる地名を探すための地名一覧をCU3～CU186セルに、上記(1)～(7)の右側の表記のように、AR6セルに表示すへき地名をCV3～CV186にセットしておくものとします。

なお、上記(1)～(7)のように住所に含まれる町名が同じだが、2つに分ける場合は、画像②のように、例えば
「北海道二海郡八雲町」⇒「八雲町」･･････(a)
「北海道二海郡八雲町熊石～」⇒「八雲町（旧熊石町）」･･････(b)
のように住所から検索される文字列の長い方を下の行にするという順番にして作成する必要があります。その上で、添付画像①のように、AR6セルに、

=IFERROR(LOOKUP(1,0/FIND($CU$3:$CU186,CE5),$CV$3:$CV$186),"")

という数式を記述することで、CE5セルの「北海道川上郡弟子屈町川湯温泉1丁目2番30号」という内容に対し、「弟子屈町（川湯地区）」という値を返しています。
つまり、ご質問者の望んでいる結果を返すようになっていると思います。

この回答へのお礼

回答ありがとうございます
詳しい説明感謝します
上手くできました

お礼日時：2023/06/07 05:51

No.1 回答者： angkor_h 回答日時： 2023/06/06 14:53

「各市町村のリスト」の次列に「AR６表示名」を設けて、

これをVLOOKUPで参照すればよいと思います。
参照値の決定には、以下でよいと思います。
　LEFT(「北海道内の各住所」、(「～」の位置)-1)

この回答へのお礼

回答ありがとうございます。
参考にさせて頂きます。

お礼日時：2023/06/06 16:33