１つのセルの文字列を、右の３つの空白でだけ分割する

質問

タイトルが分かりにくくてすみません。
１つのセルに複数の空白（半角のみ）を含む文字列があって、その最後の３つだけを分割し４つのセルに表示したいのですが・・・

例えば

A1 A2 A3 A4 A5
B1 B2 B3 B4
C1 C2 C3 C4 C5 C6

を

A1 A2/A3/A4/A5
B1/B2/B3/B4
C1 C2 C3/C4/C5/C6

のようにしたいのですが、空白の数がセルによってバラバラになっていて自分の手には負えません。

よろしくお願いします。

回答No.5

K Kazz（@JazzCorp） · ベストアンサー

Ａ列：文字列データ
Ｃ～Ｆ列：答え（最大４分割：後方より空白区切りで）
Ｇ列：ＴＲＩＭ（データ）
Ｈ列：ＬＥＮ（データ）

１行目：見出し
２行目以降：データ

Ｃ２に、
=TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(G2,F2,"")),E2,"")),D2,""))
Ｄ２に、
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(TRIM(SUBSTITUTE(G2,F2,"")),E2,""))," ",REPT(" ",H2)),H2))
Ｅ２に、
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(G2,F2,""))," ",REPT(" ",H2)),H2))
Ｆ２に、
=TRIM(RIGHT(SUBSTITUTE(G2," ",REPT(" ",H2)),H2))
Ｇ２に、
=TRIM(A2)
Ｈ２に、
=LEN(G2)

Ｃ２～Ｈ２をまとめて下にコピー

お礼

おおっ、スゴイ！
ありがとうございます。

実は、実データに他の必要ない行が含まれているで、それを排除するために、「空白が６以上の時だけ」という条件を加えたいのですが、可能でしょうか？
それが可能ならば、ベストアンサーとさせていただきます。

補足

あ、空白数は
LEN(A2)-LEN(TRIM(A2))+1
で簡単に計算できるんですね（笑）

回答No.4

noname#204879

１．Fig-1 において、次式を入力
　　　B1: =LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
　　　C1: =SUBSTITUTE(A1," ","_",B1-2)
２．Ｃ列全体を選択して、[コピー]→[値の貼り付け]を実行
３．Ａ、Ｂ列を削除
４．Ａ列全体を選択
５．[データ]→[区切り位置]を実行
６．“カンマやタブなどの…”に目玉を入れて[次へ]をクリック
７．“その他”の右のボックス内に「_」を入力して[完了]をクリック
　　　（その結果を Fig-2 に示した）
７．Ｂ列全体を選択
８．ステップ５に同じ
９．“スペース”にチェックを入れて[完了]をクリック
　　　（その結果を Fig-3 に示した）

お礼

ありがとうございます。
画像のおかげでスムーズに理解できました。

１．右から３番目のスペースを＿に置き換える
２．＿で切り離す
３．切り離した右をスペースで切り離す

という手順ですね。

これが１発でできたらサイコーなんですが・・・

回答No.3

KURUMITO

回答１です。
最後のREPTが反転してますがとのことですが、私の試験では成功しています。式をそのままコピーして使用して試験してみてください。
=IF($A1="","",IF(COLUMN(A1)=1,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),1,(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90)),IF(COLUMN(A1)=2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90,100)),IF(COLUMN(A1)=3,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-2)*100+90,100)),IF(COLUMN(A1)=4,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-1)*100+90,100)),"")))))

お礼

度々、申し訳ありません。

A1セルに
A1 A2 A3 A4 A5

B1セルに
B1 B2 B3 B4

C1セルに
C1 C2 C3 C4 C5 C6

A2セルに

=IF($A1="","",IF(COLUMN(A1)=1,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),1,(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90)),IF(COLUMN(A1)=2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90,100)),IF(COLUMN(A1)=3,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-2)*100+90,100)),IF(COLUMN(A1)=4,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-1)*100+90,100)),"")))))

をコピー＆ペーストしたんですが、同様なエラーでした。
ちなみにバージョンは２００３ですが・・・

補足

すみません、行と列を勘違いしてました。

＞B1セルに
＞B1 B2 B3 B4

＞C1セルに
＞C1 C2 C3 C4 C5 C6

＞A2セルに

↓

A2セルに
B1 B2 B3 B4

A3セルに
C1 C2 C3 C4 C5 C6

B1セルに

です。

回答No.2

mu2011

マクロですが如何でしょうか。
対象シートタブ上で右クリック→コードの表示→サンプルコード貼り付け→シート上でAlt＋F8キー押下→sample実行
因みにデータはＡ列、文字区切り数が３以上を対象とし、Ａ列より右方向に上書きしますので実行前に別シートに退避しておいて下さい。

Sub sample()
Dim i As Long, j As Long, k As Long, a, wk(3)
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
a = Split(Cells(i, "A"), " ")
If UBound(a) > 2 Then
For j = 0 To UBound(a) - 3
wk(0) = wk(0) & a(j) & " "
Next
wk(0) = Trim(wk(0))
For k = 0 To 2
wk(k + 1) = a(j + k)
Next
Cells(i, "A").Resize(, 4) = wk
Erase wk
End If
Next
End Sub

お礼

ありがとうございます。
元記事に書こうと思ってたんですが、マクロは自力で作ったことが無く、時間が掛かりそうなので（そうでもない？）、マクロを使わないものからトライしていこうと思っています。
その時はまたよろしくお願いしますｍ（＿＿）ｍ

回答No.1

KURUMITO

A1セルから下方にお示しのデータが有るとします。
B列からE列にお望みのように分割したデータを表示させるとしたらB１セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A1="","",IF(COLUMN(A1)=1,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),1,(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90)),IF(COLUMN(A1)=2,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-3)*100+90,100)),IF(COLUMN(A1)=3,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-2)*100+90,100)),IF(COLUMN(A1)=4,TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",100)),(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))-1)*100+90,100)),"")))))

" " と　"" の場合がありますので注意してください。

お礼

早速の回答ありがとうございます。

が、今やってみたのですがエラーになってしまいます。
トラブルシューティングでは、最後の'REPT'が反転してますが・・・