ひとつのセル内の文字列を複数のセルに分割する

質問

エクセルについて、初歩的かも知れませんが教えて下さい。

A1のセルに　0　85　254　などと、スペースを含む数字が並んでいます。

それをA2　B2　C2　に自動で入力したいのですが、どういった関数を使えばよいのでしょうか？

ご存知の方、よろしくお願いいたします。

回答No.3

tom04 · ベストアンサー

こんばんは！

他の方々が回答されている「区切り位置」を使うのが一番簡単な方法だと思います。
しかし、
＞自動で入力したいのですが
とありますので、何とか関数で！と頑張ってみましたが
ちょっと難しいようです。

そこでVBAになってしまいますが、一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてA1セルにデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
Dim k As Long
Dim myArray As Variant
If Target.Address = "$A$1" Then
Rows(2).ClearContents
If Target <> "" Then
myArray = Split(Target, " ")
For k = 0 To UBound(myArray)
Cells(2, k + 1) = myArray(k)
Next k
End If
End If
End Sub 'この行まで

※　スペースは半角としています。

関数で出来る方法があればごめんなさいね。m(_ _)m

補足

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

やってみました。

目的が果たせて大変嬉しいです、ありがとうございます。

しかし、複数データがありVBAって言うんですか？

私には少し難しく見えるんです！

複数データって言うのは以下のようなものです。

A3　B3　C3　に　E6　

A6　B6　C6　に　E12

A9　B9　C9　に　E43

A12　B12　C12　に　E49

A15　B15　C15　に　E55　

A18　B18　C18　に　E61　

A21　B21　C21　に　E18　

A24　B24　C24　に　E24　

A27　B27　C27　に　E30

A30　B30　C30　に　E36　

とこう言った物です。

EセルのデータをA,B,Cに振り分けます。

分割したい元データは　一桁か二桁数字　スペース　一桁数字　スペース　二桁数字です。

0 1 02 か　10　1　20　みたいな感じです。

どうぞ、よろしくお願いします。

回答No.7

MackyNo1

>E列のほかの部分には文字だったり、数字だったりのデータが存在します。

私の提示した数式は、E列の上から順に空白以外のセル（スペースが入った数字）を探し、そのデータをA～C列の3行目、6行目、9行目・・・のセルに分割する数式です。

したがって、E列にスペース数字以外のデータが入力されてる場合は、規則性がありませんので基本的に1つの関数で処理することはできません（例えばスペース数字以外のデータは先頭文字が数字ではないや対象セル以外にはスペースが含まれないなどの人間が見て判断できる規則があれば数式で対象セルを判定することも可能です）。

＞A-C列の部分は以下の様になっています。

　　　　A　　　　　　　　　　　　B　　　　　　　　　　C
１　　日付　英数字　　　　　　　漢字　　　　　　　　漢字
2　　E列の最初の数字　E列の2番目の数字　　E列の3番目の数字
3　　数字　　　　　　　　　　　数字　　　　　　　　　数字

3行目、6行目・・・・にE列の数字を分割して入れるのではないのでしょうか？
このようなケースでは「例えば」でなく、実際のセルのレイアウトを具体的に提示されたほうが解決が早いと思います。

＞教えて頂いた関数を入れてみました。

＞A3を選択し、Ctrl+G　でジャンプダイアログ　から　セル選択　セル空白を押しOKするとデータ周囲が全部選択されました。

＞そのまま関数を貼り付けして、Ctrl+Enter　で、その選択された全部のセルに他のデータが貼り付けられてしまいました。

＞どこかいけないところがあったのでしょうか？

私の提示した操作は（E列にスペース数字以外がないパターンの回答ですが）、A3セルを選択するのではなく、A列の数式を入力したいデータ範囲を選択して操作するように回答しています（Ctrl+Emterで空白セルにまとめて数式を入力することができる）。

＞また、この作業は毎日、100回近くする必要がありますので、なるべく効率的に行いたいと思っています。

この部分が具体的にどのような作業をするのか（目的が書かれていないため）良くわかりません。

同じレイアウトの（E列のセル位置が変更されない）シートでデータだけが変わる場合に、それらのデータを自動的に表示したいという意味なら、1回だけそれらのE列のセルを参照する数式を入力しておけば良いことになります。

この場合は、数式を各セルにドラッグアンドドロップする操作が必要となりますが、簡単にオートフィルだけで必要な数式を作成することができます。

いずれにしろ、どのような目的でこのような作業をするのかによって最も効率的なアプローチがありますので、実際の操作やE列のレイアウトなどを補足説明されると良いと思います。

回答No.6

tom04

No.3です。
補足を読ませていただくと・・・
E列とA～C列に表示する行に規則性がないみたいですね！

仮に関数で可能だとしても、A～C列の各行に一つずつ数式を入れてやる必要があります。
それでは大変ですので、今回もマクロでやってみました。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub セルに振り分け() 'この行から
Dim i As Long
Dim k As Long
Dim n As Long
Dim myG As Variant
Dim myArray As Variant

myG = Array(6, 12, 18, 24, 30, 36, 43, 49, 55, 61)
For k = 0 To UBound(myG)
Select Case myG(k)
Case 6
i = 3
Case 12
i = 6
Case 18
i = 21
Case 24
i = 24
Case 30
i = 27
Case 36
i = 30
Case 43
i = 9
Case 49
i = 12
Case 55
i = 15
Case Else
i = 18
End Select

Range(Cells(i, 1), Cells(i, 3)).ClearContents
myArray = Split(Cells(myG(k), 5), " ")
For n = 0 To UBound(myArray)
Cells(i, n + 1) = myArray(n)
Next n
Next k
End Sub 'この行まで

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

※　E列に1セルずつデータを入力するのであれば
前回のようにデータ変更のたびに表示することは可能です。

お望みの方法でなかったらごめんなさいね。m(_ _)m

お礼

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

まさに求めたい結果だと感じます。

データを貼り付けし直して、マクロ実行のみで求める結果にたどり着けそうです。

関数の他に、マクロって言うんですね。

こんな方法があるとは知りませんでしたが、使いこなすのは至難の技のような気がします。

なにはともあれ、再度のご回答ありがとうございます！

回答No.5

MackyNo1

E列の不規則なセルに入力されている1～2桁、スペース、1桁、スペース、2桁の数字を、A～C列に3行ごとに規則的に分割して入力したいということですね。

その場合、E列にはそれらの数字以外のセルは空白なのでしょうか？
また、A～C列の数式以外の部分はすでに入力済みのデータなのでしょうか？
また、表示した数字データはその後それらの値を数式などに使用しない（結果を表示するだけなの）のでしょうか？

一回限りの操作なら、エクセルの一般機能を利用した操作をお勧めしますが、データを変更した同じような処理を何回もしたい場合は、VBAまたは関数を使う必要があります。

例えば、E列には数字の組み合わせ以外には何も入力されておらず、A～C列にはすでにデータが入力されているなら以下のような操作で一括して必要なセルに数式を入力することができます。

A列のデータ範囲を選択して、Ctrl+Gでジャンプダイアログを出して、「セル選択」「空白セル」でOKして、そのまま数式バーに以下の数式を入力し、Ctrlキーを押しながらEnterします。

=TRIM(LEFT(INDEX($E:$E,SMALL(INDEX(($E$1:$E$100="")*1000+ROW($E$1:$E$100),),ROW(A3)/3)),2))

同様にB列を選択して、以下の数式を一括入力します。
=LEFT(RIGHT(INDEX($E:$E,SMALL(INDEX(($E$1:$E$100="")*1000+ROW($E$1:$E$100),),ROW(A3)/3)),4),1)

C列には以下の式を入力します。
=RIGHT(INDEX($E:$E,SMALL(INDEX(($E$1:$E$100="")*1000+ROW($E$1:$E$100),),ROW(A3)/3)),2)

これからフォーマットを作成するなど、A～C列に既入力データが無い場合は、A3～C3セルに上記の数式を入力し、A1～C3セルを選択して下方向にオートフィルしてください。

文字列数字ではなく、実際の数値データにしたい場合は上記の式の最後に「*1」を追加してください。

＃補足説明では操作の目的などが記載されていませんので、ひとまず関数の方法を紹介しましたが、同じ操作を繰り返す必要が無い（このような操作がしたい）ということがご希望なら、複雑な関数で対応するのではなく一般機能で対応されることをお勧めします。

補足

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

E列のほかの部分には文字だったり、数字だったりのデータが存在します。

また、

A-C列の部分は以下の様になっています。

　　　　A　　　　　　　　　　　　B　　　　　　　　　　C
１　　日付　英数字　　　　　　　漢字　　　　　　　　漢字
2　　E列の最初の数字　E列の2番目の数字　　E列の3番目の数字
3　　数字　　　　　　　　　　　数字　　　　　　　　　数字

こんな感じですが、これが繰り返し456セルと続きます。

教えて頂いた関数を入れてみました。

A3を選択し、Ctrl+G　でジャンプダイアログ　から　セル選択　セル空白を押しOKするとデータ周囲が全部選択されました。

そのまま関数を貼り付けして、Ctrl+Enter　で、その選択された全部のセルに他のデータが貼り付けられてしまいました。

どこかいけないところがあったのでしょうか？

また、この作業は毎日、100回近くする必要がありますので、なるべく効率的に行いたいと思っています。

以上、ご報告でした、よろしくお願いいたします。

回答No.4

MackyNo1

数字の個数などにより数式を修正する必要がありますが、例示のようなデータならA2セルに以下のような数式を入力して右方向にオートフィルすれば「文字列」の数字に分割することができます。

=TRIM(MID(SUBSTITUTE(ASC($A$8)," ",REPT(" ",100)),1+(COLUMN(A1)-1)*100,100))

ひとまず、文字列で数字を返す方法を提示しましたが、その後の分割した数字の使い方によって、最も合理的な操作や数式が異なりますので、どのような目的で数字を分割したいのかを具体的に説明されたほうが的確な回答が得られると思います。

補足

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

便利そうな関数ですね、使ってみたいけど、複数のデータがあるんです。

それは

A3　B3　C3　に　E6　

A6　B6　C6　に　E12

A9　B9　C9　に　E43

A12　B12　C12　に　E49

A15　B15　C15　に　E55　

A18　B18　C18　に　E61　

A21　B21　C21　に　E18　

A24　B24　C24　に　E24　

A27　B27　C27　に　E30

A30　B30　C30　に　E36　

とこう言った物です。

EセルのデータをA,B,Cに振り分けます。

分割したい元データは　一桁か二桁数字　スペース　一桁数字　スペース　二桁数字です。

0 1 02 か　10　1　20　みたいな感じです。

どうぞ、よろしくお願いします。

回答No.2

shintaro-2

どのバージョンのエクセルをお使いかわかりませんが、

リボンのデータの中に、
区切り位置というのがあります
（2003以前なら、　データ（D)→区切り位置（E)　）

そこを開くと、
　テキストファイルを開く時と同じ画面がでますので、

ウィザードに従って
　カンマやタブなどの・・・　を選択
　区切り文字としてスペースを選択
　　と進んでいけば、

お望みのことはできると思います。、

お礼

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

同じスペースに違うデータを貼り付けて何度も同じ作業をすえる必要があるので、毎回この作業をするのは現実的に無理みたいでした！

しかし、便利な機能があるものですね、エクセルって。

覚えておいて次回使いたいと思います。

ありがとうございます！

回答No.1

last_shishi

スペース区切りのテキストファイルを

「データ」－「外部データの取り込み」－「データの取り込み」

で複数セルに読み込ますことはできますが、その方法ではだめですか。

今あるファイルを、「名前をつけて保存」
ファイルの種類を、「テキストタブ区切り」で保存しておいて、

新規に作成した表に、
メニューから
「データ」－「外部データの取り込み」－「データの取り込み」
として、

保存したテキストファイルを選ぶ

元のデータ形式を、
「カンマやタブなどのク入文字・・・」にチェック
「次へ」

「スペース」にチェック
「次へ」

「完了」で

　最後に場所を指定するという方法がありますが、どうでしょうか。

お礼

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

毎日600個程度のデータがあり、大量のデータをエクセルに貼り付けて任意の場所のみ羅列するのが目的でした。

なので、少し手間が掛かりそうです。

私の説明不足でごめんなさい。

とは言えそんな方法もあるのですね！

勉強になりました。

ありがとうございます！