任意の区切り文字でsplitする方法

VBAで任意の区切り文字でsplitする方法を教えてください。

例えば


123 456 789
333 3444 66
65 22

といようなスペースあるいはタブで区切られたテキストデータがあるとします。
これを配列に格納したいのですが、
この区切りがタブ一つとか、スペース一つとか決まっていれば
split関数で簡単に配列に格納することができますが、
タブかも知れないし、スペースがかも知れないし
その数も未定の場合にはどうしたら良いですか？

replace関数を使って、まず全てのタブをスペースに変換し
その後に、複数のスペースを一つのスペースに変換し
最後にsplit関数を使うという方法しかないでしょうか？

もっとスマートな方法があれば教えてください。

No.6 回答者： nda23 回答日時： 2015/01/09 22:56

正規表現の例

Dim 正規表現 As Object
Dim 項目集合 As Object
Dim 項目個別 As Object
Dim 文字列 As String

'★テスト用の文字列を設定
文字列 = " AAA BBB" & vbTab & "CCC PP"
'★正規表現のインスタンス化
Set 正規表現 = CreateObject("VBScript.RegExp")
'★プロパティの設定
正規表現.Global = True
正規表現.Pattern = "\S+"
'★正規表現による項目集合の作成
Set 項目集合 = 正規表現.Execute(文字列)
'★項目集合から個別項目を順次処理する
For Each 項目個別 In 項目集合
　　'★個別項目を表示
Debug.Print 項目個別.Value
Next

プロパティの意味
1.Global
　文字列全体を処理するかどうかを決める。
　Trueにすると全文字列を処理する。
　Falseにすると、最初に一致した文字列を処理して
　終了する。
2.Pattern
　正規表現の本文
　"\S+"は「空白(タブも含む)でない1個以上の文字列」
　と言う意味です。漢字の空白も含めるたい時は
　"[^　\s]+"となります。後者の意味は「漢字空白、及び
　空白(タブも含む)以外の1個以上の文字列」です。
　これで分かるように”\S”と”\s”は反対の意味になっています。
　また、"[]"は「内部の文字のいすれかに該当」ということで、
　”[]の^”は否定を示します。"+"は「1個以上」ですね。

　

No.5 回答者： eden3616 回答日時： 2015/01/09 16:32

No2です。

No4様で既に具体例が出ていますが、参照先URLの例題を多少弄って回答します。

>もう少し具体的に使い方を教えていただけないでしょうか？
>示してくださったURLも見てみましたが
>どう使えば良いのか分かりませんでした。

A列の1～5行目に以下のようなデータがあるとします

　123 456＿789
　333　3444あ66
　65区切り文字22
　777
　888文字

同じ行のB列以降に以下の結果が表示されます
正規表現で「,」へ置換後、splitで配列bufに格納しセルへ出力しています。

　123　 456　789
　333  3444　 66
　 65      22
　777
　888


なお「▼正規表現で検索」の部分は、
マッチした区切り文字列を一覧で取得するために使用したので実質は不要です。


Sub Sample2()
　　'準備
　　Dim RE, strPattern As String, i As Long, msg As String, reMatch
　　Dim myTarget As Range, j As Long, buf As Variant
　　Set RE = CreateObject("VBScript.RegExp")
　　'パターン指定
　　strPattern = "[^0-9]+"
　　'正規表現の設定
　　With RE
　　　　'パターンを設定
　　　　.Pattern = strPattern
　　　　'大文字・小文字を区別
　　　　.IgnoreCase = True
　　　　'パターンを複数にマッチさせる
　　　　.Global = True
　　　　'セルの数だけ繰り返し
　　　　For i = 1 To 5
　　　　　　'対象のセルを変数に格納
　　　　　　Set myTarget = Cells(i, "A")
　　　　　　'▼正規表現で置換して配列へ格納・結果出力
　　　　　　buf = Split(.Replace(myTarget, ","), ",")
　　　　　　For j = 0 To UBound(buf)
　　　　　　　　Cells(i, j + 2) = buf(j)
　　　　　　Next j
　　　　　　'▼正規表現で検索
　　　　　　Set reMatch = .Execute(myTarget)
　　　　　　For j = 0 To reMatch.Count - 1
　　　　　　　　msg = msg & i & "行目," & j + 1 & "番目:" & reMatch(j).Value & vbCrLf
　　　　　　Next j
　　　　Next i
　　End With
　　'結果の表示
　　MsgBox msg & "以上を区切り文字として認識しました"
End Sub

No.4 回答者： WindFaller 回答日時： 2015/01/09 14:14

こんにちは。

>replace関数を使って、……（中略)
>最後にsplit関数を使うという方法しかないでしょうか？

今の話の段階では、既存の方法を使うしかないと思います。
Excelなら、QueryTable　を使えるかもしれないですが、それを「スマート」という感覚の範疇に入るのか、私は分かりません。もう少し具体性のある話でないと、現実的ではありません。

>場所によって区切り文字が違っている場合で
>未知の場合にはどうしたら良いですか？　
どんな言語でも、厳密には、未知のものに対応出来るとは思えません。
時々ある話ですが、固定長以外にも、なんとテキストで見えていたデータの中身に、区切り信号は、バイナリでした、という話もあります。

例えば、元のデータが数字が必要なら、デリミタを統一するのではなく、正規表現で、数字を拾えばよいのではないでしょうか？そして、ここで出てきたものに、Splitを掛ければよいでしょうね。

'//
Function RegPickers(myChrs As Variant)
Const DELIM As String = ","

Dim Matches As Object
Dim Match As Object
Dim buf As String
With CreateObject("VBScript.RegExp")
　.Pattern = "\d+"
　.Global = True
　Set Matches = .Execute(myChrs)
　For Each Match In Matches
　　buf = buf & DELIM & Match
　Next
　RegPickers = Mid(buf, 2)
End With
End Function

'//

ある程度のデリミタが想定できるなら、\sというワイルドカードがあります。
\s = スペース、復帰コード,タブ,改行コード,ラインフィード

'//
Function RegSepRep(myChrs As Variant)
Const DELIM As String = ","
Dim Matches As Object
Dim Match As Object
Dim buf As String
With CreateObject("VBScript.RegExp")
　.Pattern = "(\w+)\s*"　'\sをデリミタとする。
　.Global = True
　Set Matches = .Execute(myChrs)
　For Each Match In Matches
　　buf = buf & Match.SubMatches(0) & DELIM
　Next
　RegSepRep = Mid(buf, 1, Len(buf) - 1)
End With
End Function
'//

No.3 回答者： naktak 回答日時： 2015/01/09 13:17

> 全ての場合において区切り文字が同じであればそれでも良いですが

> 場所によって区切り文字が違っている場合で
> 未知の場合にはどうしたら良いですか？

もしかして、1つのデータの中で、場面に応じて区切り文字が違うとか言ってますか？
そうだとしたら、それはそもそもインターフェースとしてNGですよね。
コロコロ区切り文字が変化する時点で、もはや区切り文字としての意義がありません。

例えばreplaceや正規表現を利用したとしても、特定データの区切り文字は半角スペースであるにも
関わらず、タブを区切り文字として認識してしまって別データとする恐れもあるので、
1つのロジックで済ますことは不可能です。
何を区切り文字とするかが分からないのに、そんな都合のいい制御できるわけありません。
可能だとしたら、区切り文字による分割ではなく、固定長文字による分割でしょう。

または、そのロジックを走行させる前に1つ1つのデータとして認識できるタイミングで
区切り文字を統一(区切り文字と認識される文字は除去)させるとか、
ダブルクォーテーションで括られている範囲内は文字列であり、区切りではないとするとか、
制約を設けないとできませんよ。

No.2 回答者： eden3616 回答日時： 2015/01/09 13:00

正規表現で統一の区切り文字にしてあげてはどうでしょうか？

連続した数字以外は区切り文字であることが条件ですが、

123 456＿789
333　3444あ66
65　22

なども、『[^0-9|\n\r]+』のような正規表現で「,」に置換してあげれば以下のようになります。

123,456,789
333,3444,66
65,22

※
上記パターンはテキストエディタ（sakura）で正規表現による置換をしたため改行コード（\n\r）を除外するパターン『|\n\r』が含まれていますが、セル内に改行コードが含まれないのであれば考慮にしなくても良いかと思います。

VBAでの正規表現
http://officetanaka.net/excel/vba/tips/tips38.htm
http://codezine.jp/article/detail/1655

この回答へのお礼

ありがとうございます。

すいません、もう少し具体的に使い方を教えていただけないでしょうか？
示してくださったURLも見てみましたが
どう使えば良いのか分かりませんでした。

よろしくお願いいたします。
　

お礼日時：2015/01/09 13:23

No.1 回答者： naktak 回答日時： 2015/01/09 12:22

どういう区切り文字のインターフェースなのかをどこかのタイミングで指定させればいいだけだと思いますが。

この回答へのお礼

全ての場合において区切り文字が同じであればそれでも良いですが
場所によって区切り文字が違っている場合で
未知の場合にはどうしたら良いですか？

お礼日時：2015/01/09 12:58