Sheet1のA列に例えば群馬県内の住所のリストがあります。(架空の住所です。)
前橋市池端町3-2-21……(セルA1)
桐生市黒保根町水沼7-3-5
高崎市大沢町1-18-204
沼田市佐山町8-6-5-402
勢多郡北橘村下箱田396
多野郡吉井町岩井27-5
以下1,000件程度
Sheet2のA1:B1538に郵政公社からダウンロードした群馬県内の郵便番号一覧表があり、A列に市郡町街大字等、B列に対応する郵便番号があります。(名前定義を仮に「〒」とします)
Sheet1の住所の郵便番号を検索する必要がありますが、そのためには住所の丁目地番より左の部分(市郡町街大字等)を分離しなければなりません。
これをマクロを使わずに1個の計算式で表示したいといろいろ工夫しましたが、どうもいい知恵が浮かびません。仕方なく一旦B列(B1)に
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,9,1),8,1),7,1),6,1),5,1),4,1),3,1),2,1)
という長たらしいネスト式を使って「2~9」の数値をすべて「1」に置き換え、
C列(C1)に =LEFT(A1,FIND(1,B1)-1)
と計算式を入れて市郡町街大字等を抽出し、D列(D1)に
VLOOKUP(C1,〒,2,FALSE) と入れてようやく郵便番号を検索しております。
結果としてはこれで正しい答は出るのですが、標準搭載の関数だけで(マクロを使わずに)何かもっと簡単な方法がありはしないかという気がしてなりません。
ご存知の方があればどうか教えてください。(OS等はWinMe→Excel2000Proです。)
No.1ベストアンサー
- 回答日時:
=LEFT(A1,LENB(A1)-LEN(A1))
でどうでしょうか?
A1で言えば"3-2-21"と削除するべき部分が1バイト文字になっていますので、バイトで文字数を数えた時の長さを文字数で数えた時の長さを引けば、
2バイト文字で構成される文字の長さがでます。
イヤー驚きました。番地が半角文字であることに着目し、バイトで数えた文字数と単なる文字数との差が漢字部分の文字数になるなんて、正にコロンブスの卵。何かありそうだとは思ってましたけど私のボケ頭では思いつきませんでした。
こんな簡単なことだったんですね。仮に番地部分が全角数字&記号だったとしてもASC関数で半角にすればよいし、眼からウロコが落ちました。ありがとうございました。
No.4
- 回答日時:
こんばんは。
紹介されている数式をみて、私のオリジナルということで出しておきます。
=LEFT(A1,MATCH(FALSE,INDEX(ISERROR(MID(A1,ROW($A$1:$A$256),1)*1),,),0)-1)
これは、住所の後ろのほうに、全角が入っても区切ることが出来ます。正確に言うと、最初に出てきた数字の手前で切り分けます。
配列数式ですが、配列の確定が必要ありません。
イヤーいろんな方法があるもんですね。今のところ数式を解析して理解するまでには至ってませんが、とりあえず試してみて正しい答えが出ることが分かりました。今後ほかの面で使えそうなので参考にさせていただきます。
ありがとうございました。
No.3
- 回答日時:
ご掲示のURLを開いたところ、ASCによる全角文字の半角化部分を除き、ANo2、masa_019さんの回答と同じでしたが、説明等もあり、ある程度は理解できました。
ありがとうございました。
No.2
- 回答日時:
こんばんは。
住所の文字列の中の最初に出てくる数値より左を取り出します。
A1に住所:前橋市池端町3-2-21があったとして、B1に
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890))-1)
これで、前橋市池端町が取り出せると思います。
エラー防止のための1234567890の結合、FIND関数による0位置の検索までは分かりましたが、数値の最小値を求めるMIN関数でなぜ数値左端の位置が算出されるのか、その仕組みが今のところボンクラ頭では理解出来ておりません。しかし結果として正しく答えが出ることが分かりましたのでお礼申し上げます。
ただ、数式としてはBLUEPIXYさんの「=LEFT(A1,LENB(A1)-LEN(A1)) 」の方が簡明だという感じがしています。
いずれにしてもありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 【Excel】住所に郵便番号を付記する方法 3 2022/05/07 17:15
- Excel(エクセル) セル内の一部に別セルを差し込む 3 2022/09/18 04:39
- Excel(エクセル) エクセルの数式で教えてください。 2 2023/06/06 13:57
- Excel(エクセル) substitute関数についての質問です 3 2023/06/21 11:57
- Excel(エクセル) エクセルの数式で教えてください。 3 2023/06/06 10:13
- Excel(エクセル) EXCELでの複雑な複数条件について 4 2022/05/09 16:19
- Excel(エクセル) エクセルのマクロについて教えてください。 2 2023/02/11 14:50
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- 政治 市町村のうち、町村って必要ないのではないでしょうか? 町村は人口も少ない、もしくは面積も小さいことが 3 2022/12/22 21:57
- 政治 市町村のうち、町村って必要ないのではないでしょうか?少なくとも役場はいりませんよね。 町村は人口も少 4 2022/11/12 22:08
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
IFとIFS関数
-
Excelを無料で使うには? パソ...
-
セルの数を求めたい
-
Excelの表示についての質問
-
再質問です。マクロの修正箇所...
-
データチェックを行うエクセル...
-
エクセルで「ページレイアウト...
-
エクセルの数式バーのフォント...
-
エクセルの関数について教えて...
-
エクセルでセルに標準で入力さ...
-
Microsoft1Officeの互換ソフト...
-
シフト表をエクセルで作るとき...
-
ユーザー定義関数をアドイン登...
-
Excel VBAで全ての矢印を赤色に...
-
VBA Private Sub Worksheet_Cha...
-
2列に入っているデータを1列...
-
Excelに詳しい方! B列が「日...
-
excel2013 MonthDays 関数が使...
-
エクセルのクイックアクセスツ...
-
Excelの数式について教えてくだ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報