エクセルのセル内にて特定位置の文字だけの変換

住所録を作成していたのですが、ソフトの関係で、○○町１－２－３の住所を○○町１丁目２－３の「丁目」入りに変更することになりました。住所には△△町字1－４　さつきアパート２-１号室のような丁目が付いていない対象外の住所もあります。簡単に「丁目」に一括変換する方法あれば教えてください。丁目は１～９丁目まであります。

No.6 ベストアンサー 回答者： MackyNo1 回答日時： 2014/05/05 23:45

文字列に「丁目」がない場合は、最初に出てくる「-」をすべて「丁目」変更するという条件ならNo1の方の回答を少し修正して、ハイフンが半角と全角が混ざっていても対応できるようにした以下の数式になります。

=IF(ISNUMBER(FIND("丁目",A1)),A1,LEFT(A1,FIND("-",ASC(A1))-1)&"丁目"&MID(A1,FIND("-",ASC(A1))+1,100))

ただし、上記の数式は、「丁目」が書いていないデータの最初に出てくる半角及び全角のハイフンをすべて丁目に変換していますので、町の名前の後に丁目のない可能性のある「地区」は、フィルタ機能を利用して別に処理するなどの工夫が必要だと思います。

この回答への補足

ありがとうございます。

上記の式でイメージ掴めました。
丁目の無い対象外地域はフィルター機能で外し、対象外にしました。

何とかできそうです♪

補足日時：2014/05/06 09:33

No.8 回答者： aokii 回答日時： 2014/05/06 07:31

A列に、○○町１－２－３　等の住所が記載されているとして、

B1セルに以下の式を入れて、下にドラッグコピーしてみてください。
=LEFT(A1,FIND("－",A1)-1)&"丁目"&MID(A1,FIND("－",A1)+1,100)

この回答への補足

ありがとうございます。

上記の式はハイフンのスタイルによって出来るのですね。
「半角ハイフン」「全角ハイフン」「長音ハイフン」などが混在していたので、置き換えによって統一させたら出来ました。

補足日時：2014/05/06 09:36

No.7 回答者： MarcoRossiItaly 回答日時： 2014/05/06 00:19

関数によらないデータ処理もご紹介しておきますね。

「-」を区切り文字として、1 セル内に記入されている文字列を複数列に分割するということです。A 列に住所データがあるとします。

※実行前に住所データをバックアップしてください

(1) A 列の処理したいセル範囲あるいは A 列全体を選択
(2)「データタブ > 区切り位置」からウィザードを起動
(3)「カンマやタブなどの……」を指定して「次へ」をクリック
(4)「区切り文字」として「その他」をチェック、その右隣のボックスに「-」を入力の上、「完了」をクリック

これで添付図のように、分割されます。これを逆に「○○町1丁目2-3」というふうにつなげたい場合は、次式。

D1　=a1&"丁目"&b1&if(c1="","","-"&c1)


＞やはり難しいみたいですね。

皆さんが難しいとおっしゃっており、そのとおりなのですが、別に、数式を書いたりすること自体が難しいと言っているわけではありません。質問者さんが持っている具体的なデータがどんな感じの内容になっているのか情報が開示されないため、データ処理をしようにも、規則性も分からないということです。どんな処理を行うときも何らかの規則性を手がかりに実行するのですから、そういう状態では考えようがないです。

唯一明らかになったのは、「町以外にも住所は色々」という情報と、「丁目変換数は７～８割ぐらい」という情報のみです。

また、大抵の住所データは様々なパターンを含んでいるので、なかなか一発の処理のみで終わりとは行かないことも多いです。あらゆるパターンを回答者のほうで用意しろというのは、個人が負える負担とか、発想の限界を超えています。そういう意味でも、唯一完全な解答を示すのは難しいと皆さんおっしゃっています。途中に含まれてくるであろう一段階、一段階の処理が難しいという意味ではありません。

「7 ~ 8 割」だということが質問者さんには既に分かっているのであれば、たぶん「丁目が付く町」の種類数はある程度、限られているのではありませんか？仮にそうだとすれば、「丁目が付く町」のリストを別途、作製しておくという方法をお勧めできます。

上で説明した区切り位置だとか、住所の文字列中の都道府県・市町村、数字などを手がかりにして文字列をバラバラにし、その破片がリスト中に存在するかどうかを COUNTIF 関数などを使って調べることができます。そうすればリスト内での有無を条件として、条件に該当したときだけ D1 セルのような感じで「丁目」を付けるというのは、たやすいことです。

なお、どんな処理を行うにせよ、先に置換などの機能を使って、お手元の住所データに含まれる文字表記の揺れを修正・統一しておいてください。具体的には、全角／半角、余計なスペースの削除といったことです。元データがしっかりしていないと、テクニックを駆使してどんな処理をしようとしてもパターンが絞れず無駄骨ですし、データベースとしての価値も下がります。

この回答への補足

ありがとうございます。

住所データの修正・統一からしっかり始めてみます。

補足日時：2014/05/06 09:31

No.5 回答者： kmetu 回答日時： 2014/05/05 19:05

> 上記の混在している住所リストの中から「丁目」対象だけの番地を引っ張り出して「丁目」に一括変換したいのですが、やはり無理でしょうか＞＜

確かに住所はいろんなパターンがあるので完璧にというのは無理だと思います。

No4さんが回答されている「文字列に「丁目」がない場合は、最初に出てくる「-」をすべて「丁目」変更する」というのがある程度無難かもしれませんが、○○町5番地○○マンション504号なんかを○○町5-504と略してる場合には誤変換になってしまいますので注意が必要ですね。

No.4 回答者： MackyNo1 回答日時： 2014/05/05 17:48

>住所には△△町字1－４　さつきアパート２-１号室のような丁目が付いていない対象外の住所もあります。

上記の説明は、1-4の部分が丁目の数字ではないということと、2-1の前の部分が町名ではないという2つのことを説明しているのでしょうか？

前半部分については、残念ながら、人間が行う場合でも、1-4の部分が丁目の数字なのか識別する方法はありませんので、もちろんエクセルでも一括変換する方法はありません。
ちなみに、後半の部分だけなら、関数で最初に出てくる「-」の前の数字が「丁目」の数字だと判断できますが、この場合でも番地部分が「3丁目2番地」などと記載されていたら正確に置換することはできません）。

すなわち、以下の例のように、どのような文字列の時に「-」を「丁目」に変換するのか、規則を提示しないと判定ができないことになるわけです。

例：文字列に「丁目」がない場合は、最初に出てくる「-」をすべて「丁目」変更する。

この回答への補足

ありがとうございます。

やはり難しいみたいですね。

例：文字列に「丁目」がない場合は、最初に出てくる「-」をすべて「丁目」変更する

丁目変換数は７～８割ぐらいなので、上記例でもそれなりに早いと思われます。
宜しければ方法を教えてください。

補足日時：2014/05/05 18:04

No.3 回答者： kmetu 回答日時： 2014/05/05 16:51

訂正です。

丁目をつけない場合もとの住所を表示せずの空白にしてました
元の住所を表示する場合以下の式になります。

=IF(ISERR(VALUE(MID(A1,FIND("町",A1,1)+1,1))),A1,IF(VALUE(MID(A1,FIND("町",A1,1)+1,1))<10,LEFT(A1,FIND("町",A1,1)+1)&"丁目"& RIGHT(A1,LEN(A1)-FIND("町",A1,1)-2),""))

元のデータがA1の場合ですので、式中のA1を適宜書き換えてください。

この回答への補足

ありがとうございます。
町が入っている住所は丁目に変換できました。

しかし、町だけではなく、「新橋○丁目」とか「品川○丁目」というように町以外にも住所は色々です。
また、丁目が入っていない「大字」の住所表示もあります。

上記の混在している住所リストの中から「丁目」対象だけの番地を引っ張り出して「丁目」に一括変換したいのですが、やはり無理でしょうか＞＜

補足日時：2014/05/05 17:54

No.2 回答者： kmetu 回答日時： 2014/05/05 16:45

質問の例では条件が絞り切れませんが

○○町１－２－３は丁目をつける
○○町１－２は?
○○町１－２０３は?
○○町１－２ー３０３は?

のようなとき町の後に1から9までの数値があれば丁目にするという条件でしたら

=IF(ISERR(VALUE(MID(A1,FIND("町",A1,1)+1,1))),"",IF(VALUE(MID(A1,FIND("町",A1,1)+1,1))<10,LEFT(A1,FIND("町",A1,1)+1)&"丁目"& RIGHT(A1,LEN(A1)-FIND("町",A1,1)-2),""))

上記の式で丁目を追加することができます。

No.1 回答者： aokii 回答日時： 2014/05/05 14:43

完全ではありませんが、A1セルに○○町１－２－３の住所が記載されている場合

=LEFT(A1,FIND("－",A1)-1)&"丁目"&MID(A1,FIND("－",A1)+1,100)

この回答への補足

ありがとうございます。

やってみましたが、ダメ（エラー表示）でした。

補足日時：2014/05/05 17:45