VBA 1セルからスペース区切り 文字列の切り取り

質問

Fa0/22_______________________disabled_____1____________auto___auto_10/100BaseTX
Fa0/23_______________________notconnect___1____________full___100__10/100BaseTX
Fa0/24_______________________connected____1__________a-full__a-100_10/100BaseTX

「_」アンダースコアは半角スペースに読み替えてください。

上記のような文字列を各セルに貼り付けています。上記の例では３行です。
この中の半角スペース以外の文字列を切り出して他のセルにコピーしたいのです。

Fa0/22 disabled 1 auto auto と結果的に５つに分解したいのです。

切り出す文字数は上記の通り様々でして、半角スペース数も常に固定という訳では
ありません。

正規表現を使うのだと思いますが具体的にどう検索すればよいのか分かりません。
ご教示のほど、よろしくお願いします。

回答No.3

keithin · ベストアンサー

＞別シートへの切り出しはこんなんでよろしいのでしょうか？

こんなカンジでいいんじゃないでしょうか。

sub macro3()
　worksheets("Sheet1").range("A:A").copy _
　　destination:=worksheets("Sheet2").range("A1")

　worksheets("Sheet2").range("A:A").texttocolumns _
　　destination:=range("A1"), _
　　datatype:=xldelimited, _
　　consecutivedelimiter:=true, _
　　space:=true
end sub

＃つまり
追加のご質問はコピーがしたいということのようですから，参考にしてください。

＃それと
違うことをやりたくなったのでしたら，まず最初のご相談は解決で閉じてから，また新しいご相談として投稿し直してください。

＃オマケ
大奮発です。

sub macro4()
　dim i as long
　dim a as variant
　on error resume next

　for i = 1 to 3
　　a = split(application.trim(worksheets("Sheet1").cells(i, 1)), " ")
　　worksheets("Sheet2").cells(i, "A").resize(1, ubound(a) + 1) = a
　next i
end sub

お礼

ありがとうございます。
sub macro4()
が理解できましたのでこちらを活用させて頂きます。

追加質問の件は以後気をつけます。
ご指摘ありがとうございました。

回答No.2

keithin

方法１）
データメニュー（データタブ）の「区切り位置」を使い，「カンマやタブで区切られた…」から「スペース」にチェックを入れて文字列を切断する
という操作を新しいマクロの記録でマクロに録って，活用してみてください。

方法２）
sub macro2()
　dim h as range
　dim a as variant

　for each h in range("A1:A3")
　　a = split(application.trim(h), " ")
　　h.offset(0, 1).resize(1, ubound(a) + 1) = a
　next
end sub

＃対象範囲（A1:A3）の取得はご質問の本筋じゃないので割愛しています

お礼

ありがとうございます。

いろいろやってみたのですが、別シートへの切り出しはこんなんで
よろしいのでしょうか？（なんか非効率のような気がするのですが）

Sub macro2()
　 Dim h As Range
　 Dim a As Variant

　　　For Each h In Range("A1:A3")
　　　a = Split(Application.Trim(h), " ")
　　　h.Offset(0, 1).Resize(1, UBound(a) + 1) = a
　 Next
Worksheets("Sheet2").Range("A1:IV65536").NumberFormat = "@"
Worksheets("Sheet1").Range("B1:IV65536").Copy Destination:=Worksheets("Sheet2").Range("A1:IV65536")
End Sub

回答No.1

bin-chan

例示のデータがセルＡ１・Ａ２・Ａ３として。

セルＢ１に式[=trim(a1)]を設定、それをセルＢ２・Ｂ３にコピー。
その上で、式の結果をコピーして、形式を選択して貼り付け＞値　でペースト。
その結果（値）を、区切り位置を指定する＞スペース　で列に分割。

でどうでしょう？

お礼

ありがとうございます。
セル単位で「区切り位置を指定する」コマンドの存在を知りません
でしたので参考になりました。