エクセルの都道府県名連続入力を抽出削除するには

教えてください。
エクセルの住所リストで、

大阪府大阪府大阪市住之江・・・
東京都東京都新宿区・・・

などと、都道県名が連続で二重に入力されてしまっているデータがあります。
都道府県と市は分かれておらず、一つのセルの中に入っています。
都道府県名から番地まで、一つのセルの中に入っています。
すべてのデータではなく、問題ないデータもあれば、上記のよう連続で二重入力のデータもあります。
データは5000件、そのうち3～４割が問題だとおもわれます。
そこで、問題の連続二重入力を抽出して、一括で修正する方法を探しています。
良い方法があったら教えてください。
宜しくお願いいたします。

No.10 ベストアンサー 回答者： 銀鱗 回答日時： 2021/11/18 14:36

自分なら手作業感満載の、47都道府県の一覧を作ってそれを基に置換するかな。

数式はSUBSTITUTE関数を主に使います。

A列に対象の住所が入力されていて、
B列に修正した住所を表示させるなら、
D1セルからD47セルまで、”北海道” から ”沖縄県” までの一覧を作り、
C1セルにその連続した6文字分を表示させます。
（＝LEFT(C1&C1,6)……で神奈川県なら”神奈川県神奈"）

B1セルに
　=IFERROR(SUBSTITUTE(A1,VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE)&VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE),VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE)),A1)

考え方は……
住所の先頭から6文字と、作成した表の6文字が一致したら、正しい都道府県を2つ並べた文字列を1つに置き換える。
一致しなかったり、置換に失敗したら元の住所を表示する。
……です。

あとはB列をコピーしてA列に「値の貼り付け」をしたのちに、B列からD列を削除。
力業です。

・・・

東京都八王子市や三重県津市など、重複していない場合はVLOOKUP関数で、
秋田県秋田市や和歌山県和歌山市など、県と市の名称が同じ場合はSUBSTITUTE関数で、
それぞれエラーになるのでA列の元の住所がそのまま表示されます。
ここも力業。

見た目は長ったらしいけど、VLOOKUP関数が重複しているだけで中身は難しいものではありません。
　=IFERROR(
　　　　　　SUBSTITUTE(
　　　　　　　　　　　　A1
　　　　　　　　　　　　,VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE)
　　　　　　　　　　　　&
　　　　　　　　　　　　 VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE)
　　　　　　　　　　　　,VLOOKUP(LEFT(A1,6),$C$1:$D$47,2,FALSE)
　　　　　　　　　　　　)
　　　　　　,A1
　　　　　　)

No.9 回答者： goomania 回答日時： 2021/11/16 20:06

No.4、5です。

「都」「道」「府」「県」の文字を探して置換を行う方法が汎用性が高いと考え(例えばあとで「市」「町」などの重複が発見されたときも応用がきくという観点)で、2つの数式を投稿しました。
汎用性にとらわれなければNo.2さん、No.3さん、No.8さんの数式でも問題ないので、その考えに基づくと以下の数式でもよいと思います。

=REPLACE(A1,1,(LEFT(A1,3)=MID(A1,4,3))*3+(LEFT(A1,4)=MID(A1,5,4))*4,"")

No.8 回答者： トモクンアヤチャン 回答日時： 2021/11/16 08:23

都道府県名は3文字と4文字（神奈川・和歌山・鹿児島）があります。

=RIGHT(A1,LEN(A1)-IF(LEFT(A1,3)=MID(A1,4,3),3,IF(LEFT(A1,4)=MID(A1,5,4),4,0)))
ではどうでしょうか。先頭の3文字または4文字が繰り返しならばそれを除く（3か4か0）右側全部という作業です。

No.7 回答者： mike_g 回答日時： 2021/11/16 00:59

goomaniaさんの[No.5]のコメントを読んだ上での修正です。

=SUBSTITUTE(A1,LEFT(A1,3),"",1)
↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓↓
=IF(LEFT(A1,3)=MID(A1,4,3),SUBSTITUTE(A1,LEFT(A1,3),"",1),A1)

No.6 回答者： mike_g 回答日時： 2021/11/16 00:45

添付図参照(Excel 2019)

￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

No.5 回答者： goomania 回答日時： 2021/11/15 21:19

No.4です。

前回回答の数式で殆どのケースでは問題ないと思うのですが、レアケースとは言え、困ったことになる可能性があることに気づきました。
添付画像①をご覧ください。仮に「大阪府立大学生協」の住所が添付画像①のA1のようなものだったとします。
すると前回回答の数式だと、「大阪府」は重複していないのですが、「大阪府立大学内」に「大阪府」があるため「２番目」の都道府県名と認識され、空白に置換されてしまいます。
このような事態さけるため、あくまでも「大阪府大阪府」のように「都道府県名が繰返していたら」という条件で置換することにすると数式は添付画像➁のようになります。
この場合の数式はB1セルに

=SUBSTITUTE(A1,REPT(LEFT(A1,AGGREGATE(15,6,FIND({"都","道","府","県"},A1),1)),2),LEFT(A1,AGGREGATE(15,6,FIND({"都","道","府","県"},A1),1)))

という数式を記述して、下方向にコピーしています。
つまり、「都道府県名が繰返していたら」「都道府県名を１つにする」という数式です。
こうすれば、前回回答の数式による冒頭に記したような問題は回避できます。

No.4 回答者： goomania 回答日時： 2021/11/15 20:06

添付画像をご覧ください。

A列のA1セルから下方向に、ご質問者がお示しになった「都道府県名が重複しているものが含まれている住所データ」があるものとします。
作業列を確保して、上記データの変換を行うものとします。
ここではB列を作業列とし、B1セルに

=SUBSTITUTE(A1,LEFT(A1,AGGREGATE(15,6,FIND({"都","道","府","県"},A1),1)),"",2)

という数式を記述し、下方向へコピーしています。
こうすれば、A列の同じ行のセルのデータにが都道府県名の重複があれば削除されたものが表示され、重複がなければそのまま表示されます。

上記数式はFIND関数を用いてセルの中の「都」「道」「府」「県」の文字位置を探し、AGGREGATE関数でその文字位置を抽出、LEFT関数で先頭からその文字までの文字列を切り出して、都道府県名を特定しています。
その上で、SUBSTITUTE関数を用いて「２番目」の都道府県名を「空白」に置換しています。
つまり、「２番目」の都道府県名がない場合は置換が起こらず、そのまま表示されるというわけです。

No.3 回答者： fujillin 回答日時： 2021/11/15 16:04

こんにちは

考え方はNo2様と同様ですけれど、

A列に元の住所が並んでいると仮定して、B1セルに
=MID(A1,IFS(LEFT(A1,3)=MID(A1,4,3),4,LEFT(A1,4)=MID(A1,5,4),5,1,1),LEN(A1))
の関数式を入れて、右下のフィルハンドルをダブルクリック
（式がA列の最下行までフィルコピーされます）

必要なら、B列を選択して「コピー」-「値をペースト」で、B列の内容を固定値化します。

No.2 回答者： chonami 回答日時： 2021/11/15 15:22

=IF(MID(A1,1,3)=MID(A1,4,3),MID(A1,4,100),IF(MID(A1,1,4)=MID(A1,5,4),MID(A1,5,100),A1))

とかではどうでしょうかね？
ひねりがなくて申し訳ないですが。

No.1 回答者： GOMΛFU 回答日時： 2021/11/15 15:19

47件しかないんだから置き換えで