【エクセル文字列関数】

以下のオペレーションを可能とするエクセル関数をご存知でしょうか?

そういう関数が実在するならその関数をご教示下さい。
なければどうやれば目的が果たせるかご教示下さい。

A2セルに「浜松市高丘北1-2-3」のようなデータがあれば
B2セルに「浜松市高丘北」のデータが返ってくる。

A3セルに「浜松市佐鳴台1丁目2番3号」のようなデータがあれば
B3セルに「浜松市佐鳴台」のデータが返る。

つまり、文字列のうち、最初に出現する数字情報以降の文字をカットしたいのです。

目的は浜松市の政令指定都市移行に伴う顧客データベース変更で、
浜松市HPに掲載されている町名区名対比表にVlookup関数でぶつける作業上、
丁目・地番が邪魔なのでカットしたいのです。
(新潟市でも同じ話がある)

回答の条件
  • 1人1回まで
  • 登録:2006/12/13 12:15:13
  • 終了:2006/12/20 12:20:02

回答(5件)

id:dungeon-master No.1

dungeon-master回答回数571ベストアンサー獲得回数402006/12/13 12:47:00

ポイント35pt

マクロにしたほうがよいですね。

無理やりだけど、関数だけでやるならこんな感じでしょうか。

C1の内容 =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"1","#"),"2","#"),"3","#"),"4","#"),"5","#"),"6","#")

B1の内容 =LEFT(C1,FIND("#",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"7","#"),"8","#"),"9","#"))-1)

B1に数字の手前までが入ります。

2つのセルに分けているのは、関数のネストの制限によるものでC1をワークとして使っています。

数字が全角文字ならそのように書き換えてください。

他にも以降は無視したいという文字がある場合は、Substituteを増やしてください。

ただ、地名自体にも使われるような漢数字には対応できないと思います。

id:itarumurayama

ありがとうございます。

2006/12/13 12:54:29
id:rikuzai No.2

りくっち回答回数1366ベストアンサー獲得回数1412006/12/13 12:55:42

ポイント35pt

http://www.geocities.jp/chiquilin_site/data/050228_delimination_...

住所データの加工でしたら↑のサイトがお薦めです。

「その3」の地名までを参考に住所を分割してから、

H2=D2&E2&F2&G2

とすればH2に番地の前、地名までが表示されます。


または

1行目タイトル行、

A列が元データとして、B2に「その3」の番地以下を取り出す数式↓

=REPLACE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(222),""),CHAR(223),""),1,MIN(FIND({"1","2","3","4","5","6","7","8","9","0"},JIS(A2&1234567890)))-1,"")

に入力した上で、C2に

=LEFT(A2,LEN(A2)-LEN(B2))

とすればC2に番地以前の住所データが表示されます。


少々に複雑なことをしていますので、解説が必要でしたら教えてください。

コメント欄で補足します。

id:taknt No.3

きゃづみぃ回答回数13539ベストアンサー獲得回数11982006/12/13 13:14:50

ポイント10pt

町名区名を用いれば そんなに難しくなく分割できます。

町名区名対比表 フラグ として

浜松市高丘北 1

浜松市佐鳴台 1

というデータを作ります。

これを住所の列の最後に追加してソートします。

浜松市高丘北1-2-3

浜松市佐鳴台1丁目2番3号

浜松市高丘北 1

浜松市佐鳴台 1


並び替えをするときは、並び替えのところで オプション

方法- ふりがなを使わないに選択します。

浜松市高丘北 1

浜松市高丘北1-2-3

浜松市佐鳴台 1

浜松市佐鳴台1丁目2番3号

となります。

これで A列 住所 B列 フラグとなりますので C列の二行目から(データがある行の2列目から)

=IF(B2="",IF(B1=1,A1,C1),"")

と入れます。

これが 町名区名です。

次に D列の二行目に

=IF(B2="",RIGHT(A2,LEN(A2)-LEN(C2)),"")

と入れ

このCとDのセルをコピーして 行数分 貼り付けます。

id:kaiton No.4

kaiton回答回数260ベストアンサー獲得回数342006/12/13 18:54:13

ポイント10pt

関数ではないのですが..それと、他の方と回答がだぶったらすみません。 m(__)m


郵便番号変換ウィザードを使って

 まずは 住所→郵便番号に変換して

 その横のセルに 郵便番号→住所に変換する


参考)

http://www.eurus.dti.ne.jp/~yoneyama/Excel/benri.htm#yuubin

http://q.hatena.ne.jp/1060306617

http://q.hatena.ne.jp/1140227260

質問者が未読の回答一覧

 回答者回答受取ベストアンサー回答時間
1 nyankochan 323 288 9 2006-12-13 12:59:47
  • id:nyankochan
    オープンされなかったのでこちらへ書いておきます。
    念のため、一回の操作でガーーーっと変換できるものを作りましたので、
    せっかくなので掲載しておきますね。

    http://mewlet.com/~work/henkan.xls
    右クリックのメニューでパソコンにダウンロードしてお使いください。


    [使い方]
    顧客情報が入っているブックと、ダウンロードしたブックの両方を開きます。
    顧客情報の住所の数字以降をカットしたいデータが入っているシートを
    右クリック>シートの移動またはコピー でダイアログを開きます。
    移動先ブック名は「henkan.xls」、移動先は任意の場所で、
    「コピーを作成する」のチェックをONにして、OKを押します。

    henkan.xlsにシートがコピーされたのを確認してください。

    次に、henkan.xlsにコピーされたシートで顧客の一番上の住所データを
    選択し、エクセルのメニューの「ツール」>「マクロ」>「マクロ」、
    もしくは、Alt+F8キーを押してマクロのダイアログを開きます。

    「書出」を選択し、「実行」ボタンを押すと空白になるまで自動で
    書出シートに数字以降をカットしたデータをまったく同じセル配置で
    書き出していってくれます。


    それから・・・以下の条件が必要です。
    ・住所データが縦に並べられている
    ・ブックを開く時に「マクロを有効」を選ぶ


    住所データの途中で空白がある場合は、そこで止まるので、
    続きの場所から再度同じ操作でマクロを実行してもらえば続きを書出せます。


    文字判断の基準
    「壱」「弐」「一」「二」などの漢数字はカットしません。
    「1」「2」「1」「2」などは半角全角関係なくカットします。

    他にも転用がきくので、マクロを見れるようにしておきます。


    ファイル自体はサーバーから数日で削除するのでお早めに保存しておいて
    ください。
  • id:nyankochan
    URLをリンク処理されなかったので補足。

    http://mewlet.com/~work/henkan.xls
    にブラウザでアクセスし、「名前をつけて保存」でダウンロードできます。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません