TextToColumns

質問

VBAで以下の文字列を各セルに分解したのですがTextToColumnsでは”２つ以上の空白”だけで分解できません。なにか方法はありませんか？よろしくお願いいたします
[変換前]
2222 My name is Tokyo Male XXX@XXX
[変換後]注意：”｜”はセルの区切りとします。
2222|My name is Tokyo|Male|XXX@XXX

回答No.3

KenKen_SP

#1 です。

> スペースは２つ以上であって、いつも２つではないのです。

Split + Trim の応用でできると思いますよ^^

' // 注）投稿上の都合で□は 半角SP n個（２つ以上）に置き換える
Sub Sample2()

　　Dim s　　　As String
　　Dim vTmp　 As Variant
　　Dim vRet() As Variant
　　Dim v　　　As Variant
　　Dim i　　　As Long

　　' // テストデータ
　　s = "2222□□□My name is Tokyo□□□□□□□Male□□□□XXX@XXX"

　　' // 必要なら全角SPを半角SPに置換して統一する
　　' s = Replace$(s, "　", " ")
　　' // 半角SP2つ区切りで配列化
　　vTmp = Split(s, Space$(2))
　　' // 配列をループで回し、空の要素を取り除く
　　i = 0
　　For Each v In vTmp
　　　　v = Trim$(v)
　　　　If Len(v) Then
　　　　　　ReDim Preserve vRet(i)
　　　　　　vRet(i) = v
　　　　　　i = i + 1
　　　　End If
　　Next
　　' // 結果
　　If i > 0 Then
　　　　Range("A1").Resize(, i).Value = vRet
　　End If

End Sub

回答No.2

imogasi

>[変換前]
2222 My name is Tokyo Male XXX@XXX
の辺の例示が良くわからない。
文章ではっきり書くこと。
私はこう解釈した。
2222の後に半角２スペース、Maleの後にも半角２スペース、その他の区切りは１スペースだと。
２スペースの後だけで分けたい。
ーー
それなら、
一旦２スペースを￥（何でもよいが、残りの文字列で起こりそうにない文字。見つけにくければ組み合わせ文字列でも可？）に置換し、その￥で区切り位置の操作をした。
マクロの記録をとれば、記録できた。下記
Sub Macro1()
Cells.Replace What:=" ", Replacement:="\", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
End Sub
ヒントになるでしょう。冗長なコードは省いてください。

回答No.1

KenKen_SP

こんにちは。

一般機能でも VBA でも

　　1.　"２つ以上の空白" だけを何らかの文字に置換（例）$
　　2.　1. で設定した文字を区切り文字にする

とすれば、できるんじゃないかな？

VBA に限定すれば、Split 関数を使って配列にばらす方法もありますね。

' // 注）投稿上の都合で□は半角SP２つに置き換える
Sub Sample()

　　Dim s　As String
　　Dim v　As Variant
　　s = "2222□My name is Tokyo□Male□XXX@XXX"
　　v = Split(s, Space$(2))
　　Range("A1").Resize(, UBound(v) + 1).Value = v

End Sub

補足

KenKen_SPさん、早急のお返事ありがとうございます。
スペースは２つ以上であって、いつも２つではないのです。
ために５つ、３つ、あるいは１５つの時もあります。