１セル内のスペースで区切られた数列1360個を先頭から1個づつ抽出しするには?

質問

Excel 2003を使用しています。
Cell内に1360個の数字が並んでおり、それを1セルに1数字ずつ先頭から抽出し別のセルに振り分ける方法はありますか。区切り位置機能ではセル内に全ての数字が入りきらない為全て数字がセルに入りません。
LEFT、MIDコマンドでは文字列として扱われるためスペースが邪魔になる。
1セル内の内容
0 0 0 4 0 0 230 0 0 23 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 234 0 0 0......
↓こうしたい、
0 0 0 4 0 0 230
0 0 23 0 0 0 0
0 0 0 0 0 0 0
...........

よろしくお願いします。

回答No.9

zap35 · ベストアンサー

＃０８です。仕様を変えました。データがあるセルを選択して実行すると、新しいシートを追加しそのシートにデータを書き込むようにしました。なお７行目末尾の数字を変更すれば１行あたりの列数を変更できます。

Sub Macro5()
Dim cnt As Long, wkStr As String, ptr
Dim psw As Boolean
　wkStr = ActiveCell.Value
　Sheets.Add
　Do While psw = False
　　wkStr = Application.Substitute(wkStr, " ", "@", 7)
　　ptr = Application.Find("@", wkStr & "@")
　　Cells(1, 1).Offset(cnt, 0).Value = Left(wkStr, ptr - 1)
　　cnt = cnt + 1
　　If ptr >= Len(wkStr) Then
　　　psw = True
　　Else
　　　wkStr = Right(wkStr, Len(wkStr) - ptr)
　　End If
　Loop
　Cells(1, 1).Resize(cnt, 1).TextToColumns Destination:=Cells(1, 1), _
　　　DataType:=xlDelimited, Space:=True, Other:=False
End Sub

お礼

ご回答有難う御座いました。
私には難しいかと思いましたが丁寧に指導頂き目的通りの事ができました。大変助かりました。有難う御座いました。

回答No.8

zap35

解決済みとも思いましたがマクロを書いてみました。２行目のデータのセルアドレスは書き換えてください。アクティブセルから下にバラしたデータを展開します。実行前にはブックを保存してくださいね。
（なお区切り文字は半角の空白を想定しています。全角ならお手数ですが半角に置換してください）

Sub Macro5()
Const trgADRS As String = "A1" '←ここにデータのセルアドレスを記述
Dim cnt As Long, wkStr As String, ptr
Dim actCell As Range, psw As Boolean
　Set actCell = Selection.Cells(1, 1)
　wkStr = Range(trgADRS).Value
　Do While psw = False
　　　wkStr = Application.Substitute(wkStr, " ", "@", 7)
　　　ptr = Application.Find("@", wkStr & "@")
　　　actCell.Offset(cnt, 0).Value = Left(wkStr, ptr - 1)
　　　cnt = cnt + 1
　　　If ptr >= Len(wkStr) Then
　　　　　psw = True
　　　Else
　　　　　wkStr = Right(wkStr, Len(wkStr) - ptr)
　　　End If
　Loop
　actCell.Resize(cnt, 1).TextToColumns Destination:=actCell, _
　　　DataType:=xlDelimited, Space:=True, Other:=False
End Sub

マクロはALT＋F11でVBE画面を開き、「VBAProjectエクスプローラのシート名右クリック」→「挿入」→「標準モジュール」で表示される画面にペーストして下さい。実行はALT＋F8を押して、マクロ名を選択します。

回答No.7

DVD-RW

Excelの仕様で列数が256までなのでそれに引っかかるということですよね。

1360個の数字が一行だけでしたら、
１．そのEXCELシートをテキストファイルとして保存する
２．ノートパッド等で7つ目のスペースごとに改行してから保存しなおす
３．Excelでそのテキストファイルをインポートすれば早いでしょう。

1360個の数字が複数行あるのでしたら、VBS（Visual Basic Script)で、上の２の作業をするプログラムを作ると良いでしょう。

お礼

ご回答有難う御座いました。
データが１３６０個になり桁数もまばらで間違いを起こしやすい為
ノートパッドで見直し後の編集をするのはやめました。
有難うございました。

回答No.6

KenKen_SP

あ、、補足します。

クリップボードにタブ区切りで転送してるので、もし試すなら
[データ]－[区切り位置] でタブにチェックを入れておかないと
ペーストしたとき、分割されません。

回答No.5

KenKen_SP

こんにちは。KenKen_SP です。

VB による方法です。

標準モジュールに下記を貼り付け、Microsoft Forms 2.0 Object Library を
参照設定して下さい。

ご質問文では 7個になってましたが 1360/7 で 195 行にもなってしまいます
ので、16個区切りにしてます。コード内の COL_COUNT で変更できます。
なお、セルに分割した最後に 余計な 0 があるけど、何だか今日は頭が良く
回らなくてデバッグしてません...が、まぁ特に問題は無いと思います。

どんな出力方法が良いかなと考えたのですが、良い方法が思いつかなかった
ので結果をクリップボードに入れて、転記作業はユーザーにまかせる仕様に
しました。

Public Sub SampleProc()

　　' Microsoft Forms 2.0 Object Library の参照が必要です
　　' Visual Basic Editorのメニューから[ツール]-[参照設定]を選択し
　　' [参照設定]ダイアログボックスから

　　' 区切りをここで指定（現在16個区切り）
　　Const COL_COUNT = 16

　　Dim CPB　As DataObject
　　Dim vBuf As Variant
　　Dim vDat As Variant
　　Dim sRet As String
　　Dim j　　As Long

　　vBuf = Split(ActiveCell.Value, " ")
　　j = 1
　　ReDim vTmp(1 To COL_COUNT)
　　For Each vDat In vBuf
　　　　vDat = Val(Trim$(vDat))
　　　　If Len(vDat) > 0 Then
　　　　　　If j > COL_COUNT Then
　　　　　　　　sRet = sRet & Join$(vTmp, vbTab) & vbCrLf
　　　　　　　　ReDim vTmp(1 To COL_COUNT)
　　　　　　　　j = 1
　　　　　　End If
　　　　　　vTmp(j) = vDat
　　　　　　j = j + 1
　　　　End If
　　Next
　　sRet = sRet & Join$(vTmp, vbTab)
　　If sRet <> "" Then
　　　　Set CPB = New DataObject
　　　　CPB.Clear
　　　　CPB.SetText sRet
　　　　CPB.PutInClipboard
　　　　Set CPB = Nothing
　　　　MsgBox "クリップボードにコピーしました(´・ω・`)", vbInformation
　　End If
End Sub

お礼

とても難しいかったですが、なんとかできました。
有難う御座いました。

回答No.4

shanglee

ANo.3です。

> →プレビュー画面で全列を選択（左端の列を選択し、Shiftを押しながら右端の列をクリックすると全選択できます）＆列のデータ形式は[G/標準]にチェック
に関して補足です。このダイアログの[表示先]についてです。

0 0 0 4 0 0 230 0 0 23 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 234 0 0 0......
が[A1]セルにある場合、表示先には[$A$1]が入っていると思いますが、例えばこれを[$A$2]または[A2]に変えれば
[A1]の内容はそのままで [A2][B2][C2]・・・に分割された値が表示されるようになります。
[$A$1]のままにしておくと、区切る前の長いデータは上書きされてしまいますのでご留意ください。

回答No.3

shanglee

ANo.2の回答と似ていますが、別に一度テキストに保存する必要はありません。
現状のまま、データが入っているセルを選択して、メニューバーから[データ]－[区切り位置]をクリックしてください。

そして、[カンマやタブなどの区切り位置によってフィールドごとに区切られたデータ]にチェック
→区切り文字は[スペース]のみにチェック
→プレビュー画面で全列を選択（左端の列を選択し、Shiftを押しながら右端の列をクリックすると全選択できます）＆列のデータ形式は[G/標準]にチェック
→完了

※ 列のデータ形式は[文字列]を選択すれば文字列形式にもなります。

お礼

ご回答有難う御座いました。この方法ですとデータ数が１３６０個となり全てのデータがセルに入りませんでした。
有難う御座いました。

回答No.2

NIWAKA_0

＞0 0 0 4 0 0 230 0 0 23 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 234 0 0 0......

中身を一度、テキストファイルに保存して、
エクセルで[ファイル]→[開く]→ファイル形式を[テキストファイル]にして、
テキストファイルウィザード１／３で
[元のデータの形式]を[カンマやタブなどの区切り文字によって・・・]の方にチェック→[次へ]
２／３で、
[区切り文字]の[スペース]にチェック→[次へ]
３／３で、
[データのプレビュー]で全範囲選択して
[列のデータ形式]を[Ｇ／標準]に。
[完了]

これでどうですか？

お礼

ご回答有難う御座いました。この方法ですとデータ数が１３６０個となり全てのデータがセルに入りませんでした。
有難う御座いました。

回答No.1

mshr1962

まず７つずつに分けてみては？
A1="0 0 0 4 0 0 230 0 0 23 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 234 0 0 0......"
A2=MID($A$1,1,FIND(",",SUBSTITUTE($A$1," ",",",(ROW()-1)*7)))
A3=MID($A$1,FIND(",",SUBSTITUTE($A$1," ",",",(ROW()-2)*7)),FIND(",",SUBSTITUTE($A$1," ",",",(ROW()-1)*7)))
でA3を下方にコピー
分割できたら、コピーして値として貼り付けして、区切り位置でスペースで分割

お礼

ご回答有難う御座いました。早速やってみましたが区切り位置でスペース分割するとセルの数の制限により一部が表示できなくなりました。
有難う御座いました。