Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6480 / 13646 ツリー ←次へ | 前へ→

【44908】住所の表示 初診者 06/12/5(火) 23:37 質問[未読]
【44909】Re:住所の表示 かみちゃん 06/12/5(火) 23:45 発言[未読]
【44911】Re:住所の表示 かみちゃん 06/12/6(水) 0:08 回答[未読]
【44910】Re:住所の表示 maverick 06/12/5(火) 23:57 回答[未読]
【44930】Re:住所の表示 Tk 06/12/6(水) 21:28 質問[未読]
【44931】Re:住所の表示 かみちゃん 06/12/6(水) 22:00 発言[未読]
【44932】Re:住所の表示 maverick 06/12/7(木) 0:07 発言[未読]
【44912】Re:住所の表示 Kein 06/12/6(水) 0:23 回答[未読]
【44950】Re:住所の表示 初診者 06/12/7(木) 19:40 発言[未読]
【44952】Re:住所の表示 かみちゃん 06/12/7(木) 19:55 発言[未読]
【44982】Re:住所の表示 初診者 06/12/8(金) 18:45 発言[未読]
【45000】Re:住所の表示 Kein 06/12/9(土) 14:34 回答[未読]

【44908】住所の表示
質問  初診者  - 06/12/5(火) 23:37 -

引用なし
パスワード
   どなたか教えて下さい!

C列に個人情報が500人分くらい入っています。
住所は「東京都」からはじまり「様方」で終わります。


C1  〒123-4567 東京都台東区1丁目-15 石田ケンジ様方01-1111-1111
C2  山田太郎
C3
C4
C5  〒888-8888東京都〇〇市1番-1235山川豊様方
C6  鈴木二郎
C7  03-3333-3333
C8
C9
C10
C11 東京都足立区10番-253 木村卓也様方佐藤三郎
C12 04-4444-4444

住所部分だけが欲しいです。それ以外は削除したいです。

東京都台東区1丁目-15 石田ケンジ様方
東京都〇〇市1番-1235山川豊様方
東京都足立区10番-253 木村卓也様方

初診者・・・違う、初心者ですいませんがお願いします。

【44909】Re:住所の表示
発言  かみちゃん  - 06/12/5(火) 23:45 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>住所部分だけが欲しいです。それ以外は削除したいです。

「東京都」が何文字目から始まるかをInStr関数で探す。

さて、どこまでが住所なのですか?
それがわからないと、Excelにもわかりません。
たとえば、最後のスペース(半角または全角)から後ろは、名前だから、そこまでとか?
という条件が必要だと思いませんか?

【44910】Re:住所の表示
回答  maverick  - 06/12/5(火) 23:57 -

引用なし
パスワード
   >住所は「東京都」からはじまり「様方」で終わります。
Sub test()
  Dim i As Long, txt As String
  For i = Range("C65536").End(xlUp).Row To 1 Step -1
    txt = Cells(i, 3).Value
    If txt Like "*東京都*様方*" Then
      Cells(i, 3).Value = Mid$(txt, InStr(1, txt, "東京都"), _
      InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2)
    Else
      Rows(i).Delete
    End If
  Next i
End Sub

【44911】Re:住所の表示
回答  かみちゃん  - 06/12/6(水) 0:08 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>>住所部分だけが欲しいです。それ以外は削除したいです。
>
>「東京都」が何文字目から始まるかをInStr関数で探す。
>
>さて、どこまでが住所なのですか?

失礼しました。
住所の考え方が「〜様方」まで含めるということだったのですね。
ということは、
「東京都」と「様方」が何文字目にあるのかをInStr関数で探して、その位置を
基準として、Mid関数で取り出すというmaverickさんの方法でできると思います。
ただし、住所の終わりではなく、途中に「様方」があるようであれば、誤抽出
しますので、注意してください。

【44912】Re:住所の表示
回答  Kein  - 06/12/6(水) 0:23 -

引用なし
パスワード
   いつものように作業列(IV列)に数式を埋めて判定し、処理するコードです。

Sub Set_MyAddress()
  Dim MyR As Range

  Application.ScreenUpdating = False
  With Range("C1", Range("C65536").End(xlUp))
   With .Offset(, 253)
     .Formula = "=IF(OR(ISERR(FIND(""東京"",$C1))," & _
     "ISERR(FIND(""様方"",$C1))),1," & _
     "MID($C1,FIND(""東京"",$C1)," & _
     "FIND(""様方"",$C1)+2-FIND(""東京"",$C1)))"
     .Copy
     .PasteSpecial xlPasteValues
     Set MyR = .SpecialCells(2, 2)
   End With
   .ClearContents
   MyR.Copy .Cells(1)
  End With
  Columns("$C:$C").AutoFit
  Range("$IV:$IV").ClearContents: Set MyR = Nothing
  With Application
   .CutCopyMode = False
   .ScreenUpdating = True
  End With
End Sub

【44930】Re:住所の表示
質問  Tk  - 06/12/6(水) 21:28 -

引用なし
パスワード
   ▼maverick さん:

今晩は。いつも見させていただいています。
 横からすみません。
InStrの関数が使われていますが。
 ヘルプを見たのですが、いま一つ理解が進みません。

   Cells(i, 3).Value = Mid$(txt, InStr(1, txt, "東京都"), _
      InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2)

 の部分のコードの解説をしていただけませんでしょうか。
 よろしくお願いします。

【44931】Re:住所の表示
発言  かみちゃん  - 06/12/6(水) 22:00 -

引用なし
パスワード
   こんにちは。かみちゃん です。

> InStrの関数が使われていますが。
> ヘルプを見たのですが、いま一つ理解が進みません。
>
>   Cells(i, 3).Value = Mid$(txt, InStr(1, txt, "東京都"), _
>      InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2)
>
> の部分のコードの解説をしていただけませんでしょうか。

以下のコードを追加して試してみてください。
「東京都」「様方」が現れる位置を取得して、その位置を使って、MID関数で取り出しています。
MsgBox InStr(1, txt, "東京都")
MsgBox InStr(1, txt, "様方")
MsgBox Mid$(txt, InStr(1, txt, "東京都"), _
 InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2)

【44932】Re:住所の表示
発言  maverick  - 06/12/7(木) 0:07 -

引用なし
パスワード
   InStr については かみちゃん さんの解説で分かると思いますが、

>      InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2
これが分かりにくいかもしれないので少し・・・

例えば「A東京都B様方C」とあった場合、
「東京都」は 2 文字目〜、「様方」は 6 文字目〜にあります。
「様方」は 2 文字なので終わりは 7 文字目( 6 文字目+ 1 文字)です。
「東」から「方」の文字数は 7 文字( 6 文字目+ 1 文字) − 2 文字目 + 1 文字
(ここで + 1 文字 しないと「東」が含まれない)
なので、
7 文字( 6 文字目+ 1 文字) − 2 文字目 + 1 文字
( 6 文字目+ 1 文字) − 2 文字目 + 1 文字
6 文字目 − 2 文字目 + 2 文字
「様方」のInStr − 「東京都」のInStr + 2

InStr(1, txt, "様方") - InStr(1, txt, "東京都") + 2
で「東」から「方」の文字数が取得できます。

【44950】Re:住所の表示
発言  初診者  - 06/12/7(木) 19:40 -

引用なし
パスワード
   かみちゃんさん(?って変ですよね)
maverickさん
Keinさん

教えて頂きありがとうございました。
今流行りの胃腸炎になってしまって、お礼が遅くなり申し訳有りませんでした。

試してみたら全てちゃんと住所が表示されました。
MSGBOXでどういう処理をしているのか調べるなんて裏技みたいですね!今度使ってみます。

これで作業がとても楽になりました!
解決したので別で質問するべきですが、一度にした方が同じような事をしたい人に役立つと思うので
あと1点だけ教えてください。


表示された住所の名前の部分を伏字にできませんか?
名前を伏たい人がいますのでそれを集めて名前の部分を
何でも「●●●●」のように。

ルールがないと難しそうなので、入力してもらう名前の前に
「お名前」か「氏名」を、後ろに「様方」か「殿」は必ず
付けるようにします。

東京都台東区1丁目-15 お名前石田ケンジ様方
東京都〇〇市1番-1235氏名山川豊殿
東京都足立区10番-253お名前 木村卓也殿

        ↓

東京都台東区1丁目-15 お名前●●●●様方
東京都〇〇市1番-1235氏名●●●●殿
東京都足立区10番-253お名前●●●●殿

よろしくお願いします。

【44952】Re:住所の表示
発言  かみちゃん  - 06/12/7(木) 19:55 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>かみちゃんさん(?って変ですよね)

「かみちゃん」でいいですよ。

>ルールがないと難しそうなので、入力してもらう名前の前に
>「お名前」か「氏名」を、後ろに「様方」か「殿」は必ず
>付けるようにします。

「お名前」か「氏名」の位置をInStrで検索して、Mid関数で取り出し、
その取り出した文字列の後に、一定の文字列「●●●●様方」か「●●●●殿」を
付加するのではいけませんか?

【44982】Re:住所の表示
発言  初診者  - 06/12/8(金) 18:45 -

引用なし
パスワード
   かみちゃん お世話になっています。

>「お名前」か「氏名」の位置をInStrで検索して、Mid関数で取り出し、
>その取り出した文字列の後に、一定の文字列「●●●●様方」か「●●●●殿」を
>付加するのではいけませんか?

InStr、Mid関数がどんなものか調べてみますが、今できた所まで・・・


「様方」の後ろの名前まで入れようか悩んで、できれば欲しい部分を
増やしたいと思って改造してみました。
セルへの出力はこのくらいなら変えられますので受取人の最後に「殿」を付けます。

C1  〒123-4567 東京都台東区1丁目-15お名前 石田ケンジ様方山田太郎殿
C2  01-1111-1111
C3
C4
C5  〒888-8888東京都〇〇市1番-1235氏名山川豊様方 鈴木二郎殿03-3333-3333
C6  
C7  
C8
C9
C10
C11 東京都足立区10番-253 お名前木村卓也様方佐藤三郎殿
C12 04-4444-4444

maverickさんのを利用して

Sub test()
  Dim i As Long, txt As String
  For i = Range("C65536").End(xlUp).Row To 1 Step -1
    txt = Cells(i, 3).Value
    If txt Like "*東京都*殿*" Then
      Cells(i, 3).Value = Mid$(txt, InStr(1, txt, "東京都"), _
      InStr(1, txt, "殿") - InStr(1, txt, "東京都") + 1)
    Else
      Rows(i).Delete
    End If
  Next i
End Sub


東京都台東区1丁目-15お名前 石田ケンジ様方山田太郎殿
東京都〇〇市1番-1235氏名山川豊様方 鈴木二郎殿
東京都足立区10番-253 お名前木村卓也様方佐藤三郎殿

      ↓

東京都台東区1丁目-15お名前 ●●●●様方山田太郎殿
東京都〇〇市1番-1235氏名●●●●様方 鈴木二郎殿
東京都足立区10番-253 お名前●●●●様方佐藤三郎殿

のように、「お名前」か「氏名」〜「様方」の間を
全て「●●●●」に変えたいです。


Keinさんのは長いので理解が不十分です・・・すみません。
東京都〇〇市1番-1235氏名山川豊様方 鈴木二郎殿0
と0が付いてきます・・・?


Sub Set_MyAddress()
  Dim MyR As Range

  Application.ScreenUpdating = False
  With Range("C1", Range("C65536").End(xlUp))
   With .Offset(, 253)
     .Formula = "=IF(OR(ISERR(FIND(""東京"",$C1))," & _
     "ISERR(FIND(""殿"",$C1))),1," & _
     "MID($C1,FIND(""東京"",$C1)," & _
     "FIND(""殿"",$C1)+2-FIND(""東京"",$C1)))"
     .Copy
     .PasteSpecial xlPasteValues
     Set MyR = .SpecialCells(2, 2)
   End With
   .ClearContents
   MyR.Copy .Cells(1)
  End With
  Columns("$C:$C").AutoFit
  Range("$IV:$IV").ClearContents: Set MyR = Nothing
  With Application
   .CutCopyMode = False
   .ScreenUpdating = True
  End With
End Sub

【45000】Re:住所の表示
回答  Kein  - 06/12/9(土) 14:34 -

引用なし
パスワード
   置換処理を追加するなら、判定するための数式が複雑になりすぎるので、
maverickさんのようにループで一行ずつ見ていく形にした方がいいでしょう。
↓こんなコードで試してみて下さい。

Sub test_Rep()
  Dim i As Long, Pta As Long, Ptb As Long
  Dim Ptx As Long, Pty As Long, Ptz As Long
  Dim GetP As Long
  Dim txt As String, RepS As String

  Application.ScreenUpdating = False
  For i = Range("C65536").End(xlUp).Row To 1 Step -1
    txt = Cells(i, 3).Value
    Pta = InStr(1, txt, "東京都")
    Ptb = InStr(1, txt, "殿")
    If Pta > 0 And Ptb > 0 Then
      RepS = Mid$(txt, Pta, Ptb - Pta + 1)
      Ptx = InStr(1, RepS, "名前")
      Pty = InStr(1, RepS, "氏名")
      Ptz = InStr(1, RepS, "様方")
      If Ptx > 0 Then
       GetP = Ptx + 2
      ElseIf Pty > 0 Then
       GetP = Pty + 2
      End If
      If GetP > 0 Then
       RepS = WorksheetFunction _
       .Replace(RepS, GetP, Ptz - GetP, "●●●●")
       GetP = 0
      End If
      Cells(i, 3).Value = RepS
    Else
      Rows(i).Delete
    End If
  Next i
  Application.ScreenUpdating = True
End Sub

6480 / 13646 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free