教えて!gooグレードポイントがdポイントに!

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

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

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

教えて!goo グレード

A 回答 (10件)

自分なら手作業感満載の、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
      )
    • good
    • 0

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,"")
    • good
    • 0

都道府県名は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)右側全部という作業です。
    • good
    • 0

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



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

添付図参照(Excel 2019)


 ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄ ̄↓↓↓↓↓↓↓↓↓↓↓↓↓
「エクセルの都道府県名連続入力を抽出削除す」の回答画像6
    • good
    • 0

No.4です。


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

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

という数式を記述して、下方向にコピーしています。
つまり、「都道府県名が繰返していたら」「都道府県名を1つにする」という数式です。
こうすれば、前回回答の数式による冒頭に記したような問題は回避できます。
「エクセルの都道府県名連続入力を抽出削除す」の回答画像5
    • good
    • 0

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


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

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

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

上記数式はFIND関数を用いてセルの中の「都」「道」「府」「県」の文字位置を探し、AGGREGATE関数でその文字位置を抽出、LEFT関数で先頭からその文字までの文字列を切り出して、都道府県名を特定しています。
その上で、SUBSTITUTE関数を用いて「2番目」の都道府県名を「空白」に置換しています。
つまり、「2番目」の都道府県名がない場合は置換が起こらず、そのまま表示されるというわけです。
「エクセルの都道府県名連続入力を抽出削除す」の回答画像4
    • good
    • 0

こんにちは



考え方は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列の内容を固定値化します。
    • good
    • 0

=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))



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

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

    • good
    • 2

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

教えて!goo グレード

このカテゴリの人気Q&Aランキング