分割マクロ

質問

よろしくお願いします。写真のように、一つの文字列に空白スペースが何ヵ所かあり、それを3分割に分けたいのですが、M列からO列が欲しい結果なのです。しかし、5行目のようにスペースがないものは、N列にしたいのです‼find関数Len関数left.right関数を駆使して、やっても良いのでしょうが、5行目、6行目のようにスペースが、たくさんあるものは、時間がかかりそうです。結局のところ、最初と最後の文字列にを抜き出し、残りは、N列にしたいのです。なにか、良いマクロ教えて下さい。よろしくお願いします。尚、スペースは全角スペースです。

回答No.7

real beatin（@realbeatin） · ベストアンサー

こんにちは。お邪魔します。

> 5行目、6行目のようにスペースが、たくさんあるものは、時間がかかりそうです
解決までに時間が掛かる、という意味だったのでしょうか？
それとも、行数が多過ぎて、計算が終るのに時間が掛かる、でしょうか？
ふたつの意味で気になったのと、解決が長引いている理由を想像してみて、
指定した範囲に空白セルが存在する場合などのことも加味して、回答します。

> 最初と最後の文字列にを抜き出し、残りは、N列にしたいのです
仮にスペースが１つだけの場合があれば、中央N列を空にして、
両端M,O列に振り分ける、という仕様と理解しました。

元データにエラー値が混じっているとか、
３分割した先の文字列が255文字を超えるとか、
そういう場合にはうまく機能させられませんけれど、
結構意地悪なサンプル作って動作確認しています。
無いでしょうど、全角スペースのみ、３つ以上並んでいる場合は、
M N O列皆、空白になります。（N列に全角スペースだけ並びはしません。）

以下、色々作っていた中で妥当性のありそうなもの、
VBAを２例（簡易版と高速版）、数式を１例、です。
（VBAは、L列最下のデータ行まで処理します。）

' '　―――――――――――――――――――――――――――――
' ' // vba　簡易版　※使っているのはMid関数ではなく、Midステートメントです
Sub piyo()
Dim v, a, i As Long
　　For i = 3 To Cells(Rows.Count, "L").End(xlUp).Row
　　　　v = Cells(i, "L")　'　元の値
　　　　a = Split(v, "　")　'　区切り文字で分割した文字列配列
　　　　Select Case UBound(a)　'　区切り文字の数で分岐
　　　　Case 0: Cells(i, "N") = v
　　　　Case 1: Cells(i, "M") = a(0): Cells(i, "O") = a(1)
　　　　Case Is > 1
　　　　　　Mid(v, InStr(v, "　")) = vbCr　'　最初の区切り文字をCrに
　　　　　　Mid(v, InStrRev(v, "　")) = vbCr　'　最後の区切り文字をCrに
　　　　　　Cells(i, "M").Resize(, 3) = Split(v, vbCr)　'　Cr区切りで分割した文字列配列
　　　　End Select
　　Next i
End Sub
' '　―――――――――――――――――――――――――――――

' '　―――――――――――――――――――――――――――――
' ' // vba　大量データ向き、高速（配列入出力）版
Sub fuga()
Dim m, a, i As Long
　　m = Range("L3:L" & Cells(Rows.Count, "L").End(xlUp).Row).Value
　　ReDim Preserve m(1 To UBound(m), 1 To 3)
　　For i = 1 To UBound(m)
　　　　a = Split(m(i, 1), "　")
　　　　Select Case UBound(a)
　　　　Case 0: m(i, 2) = m(i, 1): m(i, 1) = Empty
　　　　Case 1: m(i, 1) = a(0): m(i, 3) = a(1)
　　　　Case Is > 1
　　　　　　m(i, 1) = a(0): a(0) = Empty
　　　　　　m(i, 3) = a(UBound(a)): a(UBound(a)) = Empty
　　　　　　m(i, 2) = Trim$(Join(a, "　"))
　　　　End Select
　　Next i
　　Range("M3:O" & Cells(Rows.Count, "L").End(xlUp).Row).Value = m
End Sub
' '　―――――――――――――――――――――――――――――

数式
――――――――――――――――――――――――――――――
M3
=LEFT(L3,IFERROR(FIND("　",L3),1)-1)
N3
=TRIM(MID(L3,LEN(M3)+1,LEN(L3)-LEN(M3)-LEN(O3)))
L3
=MID(L3,MAX(IFERROR(FIND("　",L3,ROW($2:$256)),1)+1),100)
――――――――――――――――――――――――――――――
L3だけは、Ctrl+Shift+Enterで確定して、数式バーに、
{=MID(L3,MAX(IFERROR(FIND("　",L3,ROW($2:$256)),1)+1),100)}
と表示され、配列（CSE）数式として設定されたことを確認します。
M3:O3 を必要なだけフィルダウンします。
――――――――――――――――――――――――――――――

以上、ご参考まで。
既出のご回答で解決に十分なものが得られていたのでしたら、
こちらの杞憂、むしろ幸い、と思っております。