Excelで任意の文字列を半角スペースで分割

質問

一つのセルに複数の内容が半角スペースで区切られて記載されているデータがあります。
これを半角スペースで分割することは可能でしょうか？

例：A列に
01 22 3 444 5555 6 77 888 999
111 222 333
11111 2 33 44 5555

これをB～J列に
B C D E F G H I J
01 22 3 444 5555 6 77 888 999
111 222 333
11111 2 33 44 5555
という様に自動的に分割・入力させたいのですが可能でしょうか？
※添付イメージを参照ください。

A列の様な形式のデータを日常的に分割する必要があり、現在は[データ>区切り位置]で手動入力を行っているのですが手間がかかる上ミスをしてしまうこともありなんとか自動化したいのです。

項目数・文字数ともデータによりバラバラなため、単にfind関数やmid関数などを使っても上手く行かず、
アイデアをいただけますと助かります。何卒よろしくお願い致します。

※実際の環境では「BS列に記載の内容をBT列以下に入力」「データの内容は【aaa.jpg bb_1.gif …】の様な画像ファイル名」となりますが質問ではシンプルにさせていただきました。

回答No.4

MackyNo1 · ベストアンサー

A列のセルをB列以降にスペースを区切り位置として分割したいなら、区切り位置の機能でウィザードの最後で表示先のセルをたとえばB1セルのようにB列のセルを指定すれば簡便に分割処理することができます（繰り返し行う操作なら、この操作をマクロに記録すればボタンをクリックするだけで処理可能です）

どうしても関数で実行したいなら、以下のような関数をB１セルに入力して右方向及び下方向にオートフィルコピーすることになります。

=TRIM(MID(SUBSTITUTE($A1," ",REPT(" ",500)),500*COLUMN(A:A)-499,500))

お礼

回答頂きました関数を用いましたところ希望通りの結果になりました。ありがとうございます！REPT関数を始めとした諸関数を組み合わせてこの様な処理も行えるのか、と目からウロコです。

回答No.7

bunjii

次のURLと殆ど同じと考えられます。
http://okwave.jp/qa/q8653686.html
数字の区切りが"AA"か" "の違いであり読み換えれば応用可能です。

違いは先頭に"AA"の代わりの" "が無いので先頭のみ別処理すれば良いでしょう。
B1=IF(A1="","",LEFT(A1,FIND(" ",A1)-1))
C1=IFERROR(MID($A1,FIND("/",SUBSTITUTE($A1," ","/",COLUMN(A1)))+1,IFERROR(FIND("/",SUBSTITUTE($A1," ","/",COLUMN(B1)))-FIND("/",SUBSTITUTE($A1," ","/",COLUMN(A1)))-1,LEN($A1))),"")
C1セルをC列から右に必要数コピーします。
更にB1から右側の必要列まで選択して下へ必要数コピーすれば完了です。
但し、Excel 2007以降のバージョンが対象です。

お礼

参考ページの紹介に加え今回の質問に沿った形での回答までいただき誠にありがとうございます。参考になりました。

回答No.6

tom04

こんばんは！
すでに色々回答は出ていますが、一番簡単な方法はデータの「区切り位置」だと思いますが
他の方法をご希望だというコトですので、VBAでやってみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, k As Long, lastCol As Long, myArry
For i = 2 To Cells(Rows.Count, "BS").End(xlUp).Row
lastCol = Cells(i, Columns.Count).End(xlToLeft).Column
If lastCol > 71 Then
Range(Cells(i, "BT"), Cells(i, lastCol)).ClearContents
End If
If InStr(Cells(i, "BS"), " ") Then
myArry = Split(Cells(i, "BS"), " ")
For k = 0 To UBound(myArry)
Cells(i, k + 72) = myArry(k)
Next k
End If
Next i
End Sub 'この行まで

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

お礼

回答・細かなご説明有り難うございます。参考になりました。いただいたマクロが実際にはどういった動作をしているのか、一つ一つマスターし自分の知識にさせていただきたく思います。

回答No.5

eden3616

(1)対象のシートを右クリック→コードの表示
(2)以下のVBAコードを記述
(3)A列に文字を入力・変更

以降(3)を行うたびにオフセットした列以降に分割した文字が入力されます。

■注意
新規で入力した場合は良いのですが、変更した場合
特に分割後の項目数が減る場合、単純に区切った文字を入力すると以前入力されていた項目が残ります。
出力する前にオフセットした列から列の最大列までの値を削除する処理を加えております。
不要であればコード内の「flag = 1」を「flag = 0」としてください。

データが入っている列の列記号にあわせて「tar_col = "A"」を変更してください。
画像ではA列にデータが入っているため現在は「A」を対象にしています。

■VBAコード

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Long
Dim j As Integer
Dim col_ost As Integer
Dim tar_col As String
Dim word
On Error GoTo era:

'データ列の列記号
tar_col = "A"
'列のオフセット距離
col_ost = 1
'セルの内容を毎回削除する場合は1、残す場合は0
flag = 1

If Target(1).Column <> Range(tar_col & "1").Column Then Exit Sub
Application.EnableEvents = False
For i = 1 To Target.Count
word = Split(Target(i), " ")
If flag = 1 Then Range(Target(i).Offset(0, j + col_ost), Cells(Target(i).Row, Columns.Count)).ClearContents
For j = 0 To UBound(word)
With Target(i).Offset(0, j + col_ost)
.Value = word(j)
If .Text <> word(j) Then
.NumberFormatLocal = "@"
.FormulaR1C1 = word(j)
End If
End With
Next j
Next i
era:
Application.EnableEvents = True
End Sub

お礼

細かなコード・カスタマイズまでを考慮いただいたご説明ありがとうございます。非常に参考となりました。また、自分自身の知識として非常にためになりました。

回答No.3

chonami

いや、元のデータを残したいなら区切り位置の3番目のダイアログで表示先に別のセルを指定すれば元のデータは残ったまま区切ったデータが表示されますよ。

補足

言葉足らずで申し訳ございません。補足ありがとうございます。今回は「自動的に分割できないか」の部分をメインとして質問させて頂いておりました。

回答No.2

eden3616

分割だけであればNo1の方のようにエクセルの「データ→区切り位置」から「スペース」で分割できます。
「元のデータを残したまま」スペースで分割したいということであればVBAで処理してください。

■コードの登録

http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/vba_tejyun.html
『VBAコード(プログラム)の登録』
の手順で以下のVBAコードを登録してください。

■使用方法

「対象とするセル範囲を選択した状態」で表示→マクロから「選択範囲を空白で分割」を選び実行してください。

■処理内容について

現在は画像のように隣の列を開始列として分解した文字を出力しています。
また必要な時に、処理の中で「01」が「1」とならないようにセルの書式を文字列へ変更する処理が含まれています。
出力先は同じ行のオフセット列へ分割出力します。
VBAコード内の「col_ost = 1」において「1」を変更することで出力先のオフセット量を変更することが出来ます。
A2～A4セルが選択されている場合、列のオフセット距離が「1」である場合、
B2～B4を開始列として右側へ分割した数だけ出力されます。
B列を飛ばしてC列から右へ出力する場合は「col_ost = 2」としてください。

■VBAコード

Option Explicit

Sub 選択範囲を空白で分割()
Dim i As Long
Dim j As Integer
Dim col_ost As Integer
Dim word

'列のオフセット距離を入力
col_ost = 1

For i = 1 To Selection.Count
word = Split(Selection(i), " ")
For j = 0 To UBound(word)
With Selection(i)
.Offset(0, j + col_ost).Value = word(j)
If .Offset(0, j + col_ost).Text <> word(j) Then
.Offset(0, j + col_ost).NumberFormatLocal = "@"
.Offset(0, j + col_ost).FormulaR1C1 = word(j)
End If
End With
Next j
Next i
End Sub

お礼

ありがとうございます。VBAの参考サイト・細かなご説明をいただき大変参考となりました。また、今後の知識としてためになりました。

回答No.1

chonami

データ→区切り位置で一度にできそうですが、どういった点がうまくいかないのですか？
むしろ、失敗の余地がなさそうなんですが。

補足

言葉が足らず失礼いたしました。
「データ>区切り位置」という手作業を行わず、関数等を用いてA列に入力することで自動的に区切られ表示されるようにしたいのです。

実際にはExcelで作成されているシステムの一部分についての質問となり、他にも処理しなければいけない部分があるため、少しでも手作業の手間を省きたいというのが主目的となります。
（質問末尾「BS列で記載の内容～」というのは実際にはBR列以前にも様々なデータが入力されてあり、それの結果がBS列以降（今回質問の部分）につながるからです。）