【VBA】正規表現

Question

２３歳OLです。

VBAと正規表現についての質問です。

▼やりたいこと

1 | 0 |1234567890 | 2014-2-22 22:22:22.06+09
という数列から
1234567890 という数字のみを抜き出したいです。
正確には２本目の|と３本目の|の間に入っている様々な数字です。
※桁数が固定されていません。

▼実際書いたコード

Sub Sample2()
Dim RE, strPattern As String, i As Long, msg As String, reMatch
Set RE = CreateObject("VBScript.RegExp")
strPattern = "☆この部分☆"
With RE
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i, 1))
If reMatch.Count > 0 Then
msg = msg & reMatch(0).Value & vbCrLf
End If
Next i
End With
MsgBox msg
Set reMatch = Nothing
Set RE = Nothing
End Sub

☆この部分に☆に何を入れればよいでしょう？
ご指導よろしくおねがいします。

Answer 1

＞「J列5行目から14行目の内容の"|"で区切った3つめを取り出して、それを各行のI列に入れる」ですか？

まさにこれです！言葉足らずですみません。。。

ということであれば、

Sub 三番目取り出し()
Dim i As Integer, msg As String, x() As String
Worksheets("sheet1").Activate
For i = 5 To 14
  x = Split(Cells(i, 10), "|")
  If UBound(x) >= 2 Then
    msg = Trim(x(2))
  Else
    msg = ""
  End If
  Cells(i, 9) = msg
Next i
End Sub

Comments

[joucomi]

解決しました！皆様長い間ありがとうございました。
ベストアンサーをいただいた以外の方も、とても勉強になり、今後のプログラムの意欲がわきました。
どこか勉強会とか行ってみたいです♪

Answer 2

これは、取り出したいのは「|」で区切られた三番目の項目だけでしょうか？

Comments

[joucomi]

三番目だけです！この三番目には何が何ケタの整数が入るかわかりません。

Answer 3

正規表現は

strPattern = "[0-9]+"

でよろしいかと。
「実際書いたコード」にある、

Set reMatch = .Execute(Cells(i, 1))

でreMatchにコレクション（配列のようなものと考えてよいです）でマッチした文字がすべて返されてきます。
ループで回して、reMatchに帰ってきた文字列をすべて連結すれば、数字のみをすべて抽出できます。
例えば、先頭のDimにjを追加しておいて、Forループのところを下記のようにしたらどうでしょう。

    For i = 1 To 10 
        Set reMatch = .Execute(Cells(i, 1)) 
        For j = 0 To reMatch.Count-1
            msg = msg & reMatch(j).Value
            WScript.echo reMatch.Count
        Next j
    Next i 

Comments

[joucomi]

＞strPattern = "[0-9]+"
一番前の整数だけ取り出すことができました。
ただ上記質問追記のせいなのでしょうか？
三番目の数字を取り出すことができません。

申し訳ないのですが、もう一度ご確認いただくことは可能でしょうか・・・。

[tohshima]

すでにたくさんの回答が付いていますが、一応・・・

3番目だけ取りたいのであれば、
reMatch(2).Value
とすれば取り出せます。

Answer 4

ようやくあなたのやりたいことが理解できと思います。
以下のコードをそのまま使ってみてください。

Sub Sample3()
Dim RE, strPattern As String, i As Long, msg As String, reMatch
Set RE = CreateObject("VBScript.RegExp")
strPattern = ".|.|(\d+) |.*"
With RE
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i, 1))
If reMatch.Count > 0 Then
msg = msg & reMatch(0).submatches(0) & vbCrLf
End If
Next i
End With
MsgBox msg
Set reMatch = Nothing
Set RE = Nothing
End Sub

Comments

[joucomi]

ご返事ありがとうございます。

すみません！追記にも書いてある通り、メッセージボックスは使わないタイプを作りたいと思っています。。。

せっかく書いていただいたのに申し訳ありません。
何かの参考にいたします。

[Guu]

ソースを見たのですが、たぶんイロイロ間違っていると思います。
完全な一般素人でしょうか？
素人でも構わないのですが、レベルが判らないとどこから伝えれば良いのか判りません。

なぜか判りませんが、４行目１０列からデータを入れることになっているのでご自分でソースを書いたわけではないのなら間違わないようにしてください。

これはなるべく貴女のソースを生かしたものです。その後に多分間違っているであろう個所も直したものも入れておきます
Sub 正規表現()
Dim re, strPattern As String, i, c As Long, msg As String, reMatch
Set re = CreateObject("VBScript.RegExp")
Worksheets("sheet1").Activate
strPattern = ".*\|.*\|(\d+) \|.*"
With re
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i + 4, 10))
If reMatch.Count > 0 Then
msg = msg & reMatch(0).submatches(0) & vbCrLf
End If
Next i
End With

For c = 1 To 5
Cells(c + 4, 9) = msg
Next c
Set reMatch = Nothing
Set re = Nothing
End Sub

Sub 正規表現2()
Dim re, strPattern As String, i, c As Long, msg As String, reMatch
Set re = CreateObject("VBScript.RegExp")
Worksheets("sheet1").Activate
strPattern = ".*\|.*\|(\d+) \|.*"
With re
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i + 4, 10))
If reMatch.Count > 0 Then
Cells(i + 4, 9) = reMatch(0).submatches(0) & vbCrLf

End If
Next i
End With

Set reMatch = Nothing
Set re = Nothing
End Sub

Answer 5

Okwaveに回答したものの焼き直しですが、

Sub 三番目取り出し()
Dim i As Long, c As Long, msg As String, x() As String
Worksheets("sheet1").Activate
For i = 1 To 10
  x = Split(Cells(i + 4, 10), "|")
  If UBound(x) >= 2 Then
    msg = msg & Trim(x(2)) & vbCrLf
  End If
Next i

For c = 1 To 5
  Cells(c + 4, 9) = msg
Next c
End Sub

他の回答のコメントで「無効なインデックス」となるのは、対象セルの中に|の個数が足りないところがあるんでしょう。上記では、それをUBound(x)で判断してます。

Comments

[joucomi]

ありがとうございます！確認してみます(^^)/

[joucomi]

試してみました。

しかしながら置き換えた結果がすべて同じセルに入ってしまいます。
セルを分割して
1234567890　　　　　　　1 | 0 |1234567890 | 2014-2-22 22:22:22.06+09
というような形にしたいです。

[otn]

質問追記のプログラムをSplitを使って書き直しただけなのですが。
追記のプログラムは、「J列5行目から14行目の内容の"|"で区切った3つめを取り出して、それらを全部改行で繋げ、繋げた結果を、I列5行目から9行目に入れる」ですが、
実はしたいことはそれでないということですか？
やりたいことをうまく書けないなら、日本語で書いても良いかと思います。

＞1234567890　　　　　　　1 | 0 |1234567890 | 2014-2-22 22:22:22.06+09
「J列5行目から14行目の内容の"|"で区切った3つめを取り出して、それを各行のI列に入れる」ですか？

[joucomi]

＞「J列5行目から14行目の内容の"|"で区切った3つめを取り出して、それを各行のI列に入れる」ですか？

まさにこれです！言葉足らずですみません。。。

Answer 6

正規表現で処理をすることが重要なのであればスルーしていただくとして
この処理の場合 msg = Split(対象文字列, "|")(2) のほうが手っ取り早くないですか？

Comments

[joucomi]

ＶＢＡ内の処理として　対象文字列にはどうやって入れるのでしょうか？
セルのナンバー
たとえばmsg = Split(cells(1.1), "|")(2) のような形にするのでしょうか？

[yunn]

そうです。追記のコードでいえば Cells(i + 4, 10) ですね。

[joucomi]

Sub 正規表現()

Dim re, strPattern As String, i, c As Long, msg As String, reMatch

Set re = CreateObject("VBScript.RegExp")
Worksheets("sheet1").Activate
With re
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i + 4, 10))
If reMatch.Count > 0 Then
msg = Split(Cells(i + 4, 10), "|")(2) ’☆
End If
Next i
End With

For c = 1 To 5
Cells(c + 4, 9) = msg
Next c
Set reMatch = Nothing
Set re = Nothing
End Sub
これで書いてみたのですが動かないです・・・・。
インデックスの有効な値がないそうで☆のところに黄色いラインが入ります

[yunn]

ごめんなさい、正規表現使わない方法なので

Sub 正規表現()
Worksheets("sheet1").Activate
For i = 1 To 10
msg = Split(Cells(i + 4, 10), "|")(2)
Next i
For c = 1 To 5
Cells(c + 4, 9) = msg
Next c
End Sub

こうかな？
…検索は10件で出力は5件でいいんですかね…？

[joucomi]

Sub 正規表現()

Dim msg As String
Dim i As Long
Dim c As Long

Worksheets("部分ＲＥＳＵＬＴ").Activate
For i = 1 To 5
msg = Split(Cells(i + 4, 10), "|")(2)　’ここに無効なインデックスと表示されます。
Next i
For c = 1 To 5
Cells(c + 4, 9) = msg
Next c
End Sub

こうやって書いてみましたー。
検索5件で　出力5件です。

[joucomi]

やっぱり動きませんねーどうしてでしょうか。

[yunn]

うーん(^_^;)
無効なインデックスってことは多分 "|" で Split が出来てないんですね。
Sub test()
MsgBox Split(" 1 | 0 |1234567890 | 2014-2-22 22:22:22.06+09", "|")(2)
End Sub
これで、正しく "1234567890" がMsgBoxで表示されるかどうか見てみて
これも動かない、となると…バージョンとか環境とかが（私のPCとは）異なる、ということで、出来ないやり方なのかも知れません。

正規表現のほうで正解に近付いていたなら、そちらのほうがいいかもです。

[joucomi]

Sub test()
MsgBox Split(" 1 | 0 |1234567890 | 2014-2-22 22:22:22.06+09", "|")(2)
End Sub

これは表示できました！

[joucomi]

今試してみたところ

1  1 |   0 |12345678 | 2014-2-22 22:22:22.06+09 
1  1 |   0 |1234567890 | 2014-2-22 22:22:22.06+09 
1  1 |   0 |1234567890 | 2014-2-22 22:22:22.06+09 
1  1 |   0 |123 | 2014-2-22 22:22:22.06+09 
1  1 |   0 |1 | 2014-2-22 22:22:22.06+09 

このような形でスプリットしました！
※最初の１がなぜか抽出された結果です。。。
どうしてこうなってしまうんだろう。。。

Answer 7

「strPattern = "^\d*$"」または「strPattern = "^\d+$"」で、
MsgBoxに「1」「0」「1234567890」が表示されます。
「1」しか表示されない場合は、行と列が逆になってます。
1桁を省くなら（２桁以上）、
「strPattern = "^\d.\d*$"」で、MsgBoxに「1234567890」が表示されます。

Answer 8

strPattern = " .|.|\d+ |.* "
これで良いと思います
目的の数列を抜き出したいのなら、" .|.|(\d+) |.* "とカッコで括って、submatchを見れば良いと思います。

Comments

[Guu]

最後の列をタイムスタンプと仮定すれば以下のようにして精度を上げることができます。
".*\|.*\|(\d+) \| \d{4}\-\d{1,2}\-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}.\d\d+\d\d"

固定条件を出来るだけ絞ればマッチ精度はあがります

[joucomi]

ご返事ありがとうございます。
上記いただいたコードが使えませんでした。。。。

[Guu]

？？
以下で確認できましたよ
疑わしきは、ダブルコーテーションは、正規表現ではなく、文字列の区切りとして入れてあります。
なので以下のように書きます。
あとは文字列の始まりのスペースがあいまいなので必要無ければ削ってください。

strPattern = ".*\|.*\|\d+ \|.*"
又は
strPattern = ".*\|.*\|(\d+) \| \d{4}\-\d{1,2}\-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}.\d\d+\d\d"

[joucomi]

Sub 正規表現()
Dim re, strPattern As String, i, c As Long, msg As String, reMatch
Set re = CreateObject("VBScript.RegExp")
Worksheets("sheet1").Activate
strPattern = ".*\|.*\|(\d+) \| \d{4}\-\d{1,2}\-\d{1,2} \d{1,2}:\d{1,2}:\d{1,2}.\d\d+\d\d"""
With re
.Pattern = strPattern
.IgnoreCase = True
.Global = True
For i = 1 To 10
Set reMatch = .Execute(Cells(i + 4, 10))
If reMatch.Count > 0 Then
msg = msg & reMatch(0).Value & vbCrLf
End If
Next i
End With

For c = 1 To 5
Cells(c + 4, 9) = msg
Next c
Set reMatch = Nothing
Set re = Nothing
End Sub

こうやって見ましたが、
反応しませんねー。

cells(10,5)に入っているものをcells(10,4)に移しながら下降していくのができません。

[Guu]

それは正規表現文字列の最後がダブコーテーション（”）だらけだからです

Answer 9

以下のパターンを投げ込めば、
\s*([0-9]*)\|\s*([0-9]*)\|\s*([0-9]*)\|(.*)
reMatch(3).Valueで拾えそうですね。

ただ、VBAの正規表現には詳しくないので間違えているかもしれません。