人に聞けない痔の悩み、これでスッキリ >>

こんにちは

名簿をタックシールに打ち出します。

住所はB列に、下記の例の様に

東京都六王子市海山川町88-888青空荘88号室
などとなっています。

これを、

C列に
東京都六王子市海山川町88-888

D列に
青空荘88号室

と分けたいのですが、よい方法を教えて下さい。

住所の番地には、「丁目」「番地」「号」などは入らず、
すべて半角数字で88-888などとなっています。

Excel2007です。
よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (2件)

>住所の番地には、「丁目」「番地」「号」などは入らず、


すべて半角数字で88-888などとなっています。

この条件なら、非常に複雑な数式になりますが、以下の配列数式を使用すればご希望の分割ができます。

元データがB2セルにある場合、住所部分を表示するC2セルに以下の数式を入力(コピー貼り付け)し、Ctrl+Shift+Enterで確定して(数式が大かっこで囲まれて配列数式になる)、下方向にオートフィルしてください。

=LEFT(B2,MIN(FIND({1,2,3,4,5,6,7,8,9},B2&123456789))+MATCH(0,(MID(MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9},B2&123456789)),50),ROW($A$1:$A$20),1)="-")+ISNUMBER((MID(MID(B2,MIN(FIND({1,2,3,4,5,6,7,8,9},B2&123456789)),50),ROW($A$1:$A$20),1)*1)),)-2)

それ以降の文字列を表示するD2セルには以下の数式を入力して下方向にオートフィルします。

=SUBSTITUTE(B2,C2,"")
    • good
    • 9

ビルやマンション名はある意味何でもありです。


たとえば、
東京都渋谷区宇田川町1-11にある109コーポと
東京都渋谷区宇田川町1-1にある1109コーポはどちらも続けて書けば
東京都渋谷区宇田川町1-11109コーポになってしまうように、
番地のあとで数字で始まる建物名があったら、Excelどころか、ユーザーにすらそれを区別する手段がありません。
せめて建物名の直前にスペースでもあれば別なのですが。

それでもなんとかおのぞみに近い形になるのではないかという計算式やマクロの記述が下記です。
http://www.geocities.jp/chiquilin_site/data/0502 …
http://oshiete.goo.ne.jp/qa/3796966.html

ほかこのサイトではこの質問をOKWaveで見ればよく似た目的の質疑がヒットします。
http://oshiete.goo.ne.jp/qa/8897861.html
ページ下部に質疑が並んでいます。
ご自分が作成したい内容に一番合致したものをお使い下さい。
    • good
    • 0

このQ&Aに関連する人気のQ&A

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

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q住所録からマンション名以下を別セルへ

住所が一つのセルに入っているものを
マンション名、もしくはなんとかハイツ系、なんとか荘系、
とはいってるものは別セルに表示したいのです。


A1 東京都○○区淡路町2-1-11光マンション222
A2 大阪府○○区外井場3-1-1
A3 広島県○○区草薙場5-3-1 水蔵荘20号

この場合
B1「東京都○○区淡路町2-1-11」別セルC1にて「光マンション222」
B2 「大阪府○○区外井場3-1-1」でOK
B3 「広島県○○区草薙場5-3-1」別セルC3にて「水蔵荘20号」

となるようにするためにはどしたらいいんでしょうか?

住所の全角数字はすべて半角数字には置換しました。

可能かなと思える検索条件は
マンション前には半角の"1,2,3,4,5,6,7,8,9,0,半角スペース"
があり、その後は全角の文字でマンション名がある
という事なんです。

つまり、最後の半角の後に全角の文字があるものはという事なんで
大丈夫だなと思っていたつもりだったのですが

よく考えたら、マンションの最後の部屋番号も半角文字だ。。。。

さらに問題はマンション名が半角カタカナのものもある。
※ただ、これについては最悪、置換で全角に変えようかなとも思います。
(その前に半角カナを全角カナに変える関数ってあるんですか?)

さて、これらの条件でマンション名以降だけを別セルにすることできるのでしょうか?
もしわからなければ、ウン万行のデータを手作業で・・・行わないといけないのです・・・・・。

ぜひ、よろしくお願いいたします。

住所が一つのセルに入っているものを
マンション名、もしくはなんとかハイツ系、なんとか荘系、
とはいってるものは別セルに表示したいのです。


A1 東京都○○区淡路町2-1-11光マンション222
A2 大阪府○○区外井場3-1-1
A3 広島県○○区草薙場5-3-1 水蔵荘20号

この場合
B1「東京都○○区淡路町2-1-11」別セルC1にて「光マンション222」
B2 「大阪府○○区外井場3-1-1」でOK
B3 「広島県○○区草薙場5-3-1」別セルC3にて「水蔵荘20号」

となるようにするためにはどしたらいいんでしょうか?

住所の全...続きを読む

Aベストアンサー

#01です。
少しだけマクロを変えました。「半角数字と全角カナ」「半角数字と空白」も区切りと考えるようにしています

>そこでとまってしまってます。
これを前回説明した場所に貼り付けたら、VBE画面は閉じてしまってかまいません。ワークシート画面に戻って適当なセルに、たとえば
 =GetSep(A1)
のように式を入れてみてください。ブックを保存すればマクロも一緒に保存されますので、保存を意識する必要はありません。

私は
 B1の式は  =LEFT(A1,getsep(A1))
 C1の式は  =TRIM(RIGHT(A1,LEN(A1)-getsep(A1)))
にしています。

Function GetSep(ByVal trg As Range) As Integer
Dim RE, mchItems
Dim strPattern As String
Dim idx As Integer
If trg <> "" Then
  Set RE = CreateObject("VBScript.RegExp")
  strPattern = "-[0-9]+|[0-9][ァ-ン]|[0-9] |[0-9] "
  With RE
    .Pattern = strPattern
    .IgnoreCase = True
    .Global = True
    Set mchItems = .Execute(trg.Value)
    If mchItems.Count > 0 Then
      GetSep = mchItems.Item(mchItems.Count - 1).FirstIndex _
          + mchItems.Item(mchItems.Count - 1).Length
    Else
      GetSep = Len(trg.Value)
    End If
  End With
  Set RE = Nothing
End If
End Function

結果はこうなりました。到底「完全なレベル」にはなりません。
ただ住所の末尾 1-12-5 とマンション名の間に空白を入れると分離できますので、うまくいかないデータはそのように修正してください

B列                         C列
○○府○○市○○丘1-12-5          セ○○○○泉丘101号
○○県○○市○○野区東○○台1-3-3      佐藤ハイツ2N
○○都○○区大○○町3-22-21         ○○○○園403
○○都○○区○○谷3-42-14          ス○○山101
○○県○○市○○町1234-1           ○○○○エストe502
○○県○○市○○区○○町1-20         コーポ○○102
○○県○○市○○西2-3-2            グリーン○○西101
○○県○○市○○郷4321-4           サニー○○204
○○県○○市○○区○○町5472          ○○備前303
○○県○○市○○町方五23-4           スミエ-ルミクラシ05号
○○府○○市○○区○○町鷲見99パ        ルテオン東山南209 → NG(なんでかなぁ)
○○府○○府○○市○○○○台3丁目4番129-101   号 → NG(ルール通り)
○○県○○県○○市○○○○12-2135  
○○府○○市○○町5-12             坂本ハイツ3-B
○○県○○市○○町821エステートブリッツンC-101  → NG(ルール通り)

なお#02式はすごいですね。ちょっと考えましたが動く理由がまだ理解できません。いつもながらmaron--5さんの作る関数式には感心します。

#01です。
少しだけマクロを変えました。「半角数字と全角カナ」「半角数字と空白」も区切りと考えるようにしています

>そこでとまってしまってます。
これを前回説明した場所に貼り付けたら、VBE画面は閉じてしまってかまいません。ワークシート画面に戻って適当なセルに、たとえば
 =GetSep(A1)
のように式を入れてみてください。ブックを保存すればマクロも一緒に保存されますので、保存を意識する必要はありません。

私は
 B1の式は  =LEFT(A1,getsep(A1))
 C1の式は  =TRIM(RIGHT...続きを読む

QExcelで住所を2つ(町名迄と番地以降)に分けるには?

お世話になります。
Winxp、Excel2003を使用しています。
以下のような住所データがあります。
これを、町名迄と番地以降の2つのセルに分けるには、どうすれば良いでしょうか?よろしくお願いします。

セルA1に東京都小平市東町5-8-15
      ↓  
セルA2には東京都小平市東町
セルA3には5-8-15
としたい。

Aベストアンサー

こんにちは~

A2セルに
=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890))-1)

A3セルに
=SUBSTITUTE(A1,A2,"")

と入れてみてください。

Q【Excel】住所を分ける方法

こんにちは

DM発送用の名簿を整理しています。

F列に、住所欄に 都道府県 市町村 町名番地 建物名 号室 までが
一つのセルに入力されています。

小さめのタックシールの為、印字する際、
都道府県 市町村 町名番地の文字が、
12文字を超えると見にくくなります。
※それを超えても文字を縮小して全体を印字します。

番地と建物名の間にスペースがあるので、そこで分けるようにして
建物名および号室は、H列に移動しました。

住所(都道府県 市町村 町名番地)の文字が12文字を超える場合、
町名のところでG列に分ける方法を教えて下さい。

東京都六王子市海山川町88-888 青空荘88号室 このような住所の場合、

青空荘88号室 はH列に移動しました。

東京都六王子市海山川町88-888 は、12文字を超えるため、
列を分けたいのですが、

東京都六王子市海山川町8 | 8-888
とならないように、
東京都六王子市海山川町| 88-888
若しくは、
東京都六王子市|海山川町88-888

となるようにしたいと思います。

Excel2013です。

よろしくお願いします。

こんにちは

DM発送用の名簿を整理しています。

F列に、住所欄に 都道府県 市町村 町名番地 建物名 号室 までが
一つのセルに入力されています。

小さめのタックシールの為、印字する際、
都道府県 市町村 町名番地の文字が、
12文字を超えると見にくくなります。
※それを超えても文字を縮小して全体を印字します。

番地と建物名の間にスペースがあるので、そこで分けるようにして
建物名および号室は、H列に移動しました。

住所(都道府県 市町村 町名番地)の文字が12文字を超える場合、
町名のところでG列...続きを読む

Aベストアンサー

日本の全住所を分解するわけではないと思うので、とりあえず試行してみます。
住所が12文字を超えた場合、区>町>市>村の順で検索しています。、

A1に住所があるとして、B1とC1に分けてみます。後は人の目で確認が必要です。


B1に

=IF(AND(A1<>"",LEN(A1)>12),IFERROR(LEFT(A1,FIND("区",A1)),IFERROR(LEFT(A1,FIND("町",A1)),IFERROR(LEFT(A1,FIND("市",A1)),IFERROR(LEFT(A1,FIND("村",A1)),"")))),A1&"")


C1に、

=IFERROR(RIGHT(A1,LEN(A1)-LEN(B1)),"")



B1の算式を並べると、(改行しているのでコピーできません)

=IF(AND(A1<>"",LEN(A1)>12),
  IFERROR(LEFT(A1,FIND("区",A1)),
  IFERROR(LEFT(A1,FIND("町",A1)),
  IFERROR(LEFT(A1,FIND("市",A1)),
  IFERROR(LEFT(A1,FIND("村",A1)),"")))),
  A1&"")

となっています。何をしているかは自明と思います。
住所にはいろいろなパターンがあります。入力ミスもあるかもしれません。式の結果は完全ではないので確認してください。

日本の全住所を分解するわけではないと思うので、とりあえず試行してみます。
住所が12文字を超えた場合、区>町>市>村の順で検索しています。、

A1に住所があるとして、B1とC1に分けてみます。後は人の目で確認が必要です。


B1に

=IF(AND(A1<>"",LEN(A1)>12),IFERROR(LEFT(A1,FIND("区",A1)),IFERROR(LEFT(A1,FIND("町",A1)),IFERROR(LEFT(A1,FIND("市",A1)),IFERROR(LEFT(A1,FIND("村",A1)),"")))),A1&"")


C1に、

=IFERROR(RIGHT(A1,LEN(A1)-LEN(B1)),"")



B1の算式を並べると、(改行しているのでコ...続きを読む

QExcelで1セル内の住所を市郡町街と丁目以下の地番等に分離する方法は?

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です。)

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の住所の郵便番号を検索する必要がありますが、その...続きを読む

Aベストアンサー

=LEFT(A1,LENB(A1)-LEN(A1))
でどうでしょうか?
A1で言えば"3-2-21"と削除するべき部分が1バイト文字になっていますので、バイトで文字数を数えた時の長さを文字数で数えた時の長さを引けば、
2バイト文字で構成される文字の長さがでます。

QエクセルのIF関数で、文字が入力されていたならば~

エクセルのIF関数で文字が入力されていたならば~、という論理式を組み立てたいと思っています。

=IF(A1="『どんな文字でも』","",+B1-C1)

A1セルに『どんな文字でも』入っていたならば、空白に。
文字が入っていなければB1セルからC1セルを引く、という状態です。

この『どんな文字でも』の部分に何を入れればいいのか教えてください。

またIF関数以外でも同様のことができれば構いません。

宜しくお願いします。

Aベストアンサー

=IF(ISTEXT(A1),"",B1-C1)

でどうでしょうか?

Qエクセルで「丁番地」を変換する方法を教えて下さい

エクセル2000を使っています。
セルに「1丁目2番3号」という住所があるとして
それを「1-2-3」というふうに変換したいのですが
どのようにすればいいでしょうか?

ちなみに「1丁目2番3号」の前には都道府県および
市区町村の情報も含まれております。

宜しくお願いします。

Aベストアンサー

入ってるセルがD2として
=IF(COUNTIF(D2,"*番*号*"),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"丁目","-"),"番地","-"),"番","-"),"号",""),D2)

セルに入力された文字に番と号が両方あるときだけ変換します。

Qエクセル 数値だけ抽出するには?

エクセルで、文字列+スペース+数字と入力されているセルから数値だけ取り出す方法を教えていただけますか?
文字列とスペースの字数は一定で、数値の桁数は6-8桁と変動します。
A列からb列のように変換したいのです。

A列          B列
PMID:_12345678    12345678
PMID:_123456     123456

宜しくお願いいたします。

Aベストアンサー

B1に
VALUE(REPLACE(A1,1,6,""))
でよいでしょう.

数値の桁数には依存しません.

Q2つのシート間での重複データのチェック

Excelについて教えてください。
以下の2つのシートがあります。

Sheet1
すずき 03-0000-0000
やまだ 03-1111-1111
たなか 03-2222-2222

Sheet2
03-1111-1111
03-4444-4444
03-0000-0000
03-2222-2222

Sheet1には名称と電話番号、Sheet2には別所から抽出した電話番号のみのデータがあります。
Sheet1のデータの中からSheet2に電話番号があるものだけを知りたいのですが、たとえばSheet1の 各行のC列 に Sheet2に一致する番号があった場合は●等付けるにはどうすればよいでしょうか。

よろしくお願いします

Aベストアンサー

シート1のC列に式を入れます。
=IF(ISNA(VLOOKUP(B1,Sheet2!A:A,1,FALSE))=TRUE,"-","●")

【式の説明】
シート1のセルB1の値が、シート2のA列に、存在する場合は●を、存在しない場合は-をセット。

Qエクセルで、頭に0をつけて数字の桁を揃える方法

エクセルで、必ず5桁の数字を入れる列があるとします。
ところが、数値データとして最初に作っているので、0が頭になく桁が足りない場合があります。
任意文字として5桁の数字にしたいのですが、

何かいい方法はありませんでしょうか?

例)
A列 A列
50      00050
1000  ⇒  01000
11111    11111

どうぞよろしくお願いいたします。

Aベストアンサー

次の方法は如何でしょうか。
(1)表示だけで良いのならば、セルの書式設定→表示形式→ユーザ定義で00000を設定
(2)データとして必要ならば、空き列(仮にB列)B1に=TEXT(A1,"00000")を入力、下方向にコピー→B列をコピー→A列に形式を選択して貼り付けで「値」を選択→OK、B列を削除

Q住所のセル分割について

お尋ねします
A1のセル→**山田町1-1-1山田アパート**
のように番地とアパート・マンション名が続けて入っているセルがいくつもあります。
これを
B1→**山田町1-1-1
C1→山田アパート**
のように2つのセルに分割する方法はありませんでしょうか。
番地とアパート名の間にはなにもありません。
また番地までの住所の長さはまちまちです。
よろしくお願いいたします。

Aベストアンサー

非常に微妙な論理を使ってますが、下記を実行して、ほぼうまく行けば使って見てください。
(1)テストとして、100行分のぐらいの住所を別シートのA列にコピーする。そして下記を実行し、B、C列に分離された内容が、圧倒的行数で正しいかチェック。不適当が少数なら手で修正する。
ロジックは文字部の塊が終わり、数字かハイフンが出現し、再び文字の現れた個所以下を気付・アパートとする。
Sub test01()
For i = 1 To 100 '1000行までなら1000に変える
s = Cells(i, "A") '住所のある列、C列なら"C"
For j = 1 To 30 '30は1000人の住所全体の最大文字数
c = Mid(s, j, 1) '住所のj番目文字を問題にする
Select Case c
Case Is = IsNumeric(c) '数字か
flg = 1
Case "0" 'ゼロか
Case "-" 'ハイフンか
Case "-" 'ハイフンか
Case Else
If flg = 1 Then
Cells(i, "B") = Mid(s, 1, j - 1) '分離後住所本体部を置く列
Cells(i, "C") = Mid(s, j, 10) '気付アパート部を置く列
flg = 0
GoTo p01
Else
flg = 0
End If
End Select
Next j
p01:
Next i
End Sub
(2)旨くいく条件
町字までの住所本体には数字や-がないとする.
丁・番・号には数字か-しかないものとする。1丁目とか2番地などの表現がないこと。
VBAの実行はシートでALT+F11
VBE画面で挿入(I)、標準モジュール(M)で出てくる画面に貼りつけ、F5キーで実行。
(テスト)
静岡市山田1-2山田マンション1234
仙台市新町1-2-3ABCアパート
大手町2-3-4富士ビル3階
大阪市中央区大手町1-20沖縄
大阪市北区駅前1-21希望1-234
東京都千代田区大手町3-3-3日本ビル123
埼玉県さいたま市宮前2-3-4 金剛333
埼玉県さいたま市宮前2-3-4
C列は
山田マンション123
ABCアパート
富士ビル3階
沖縄
希望1-234
日本ビル123
 金剛333
となりました。

非常に微妙な論理を使ってますが、下記を実行して、ほぼうまく行けば使って見てください。
(1)テストとして、100行分のぐらいの住所を別シートのA列にコピーする。そして下記を実行し、B、C列に分離された内容が、圧倒的行数で正しいかチェック。不適当が少数なら手で修正する。
ロジックは文字部の塊が終わり、数字かハイフンが出現し、再び文字の現れた個所以下を気付・アパートとする。
Sub test01()
For i = 1 To 100 '1000行までなら1000に変える
s = Cells(i, "A") '住所のある列、C列な...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング