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

Question

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

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

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

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

tom04 · Accepted Answer

こんばんは！

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

そこで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

MackyNo1 · Answer

>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列のレイアウトなどを補足説明されると良いと思います。

tom04 · Answer

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

MackyNo1 · Answer

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」を追加してください。

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

MackyNo1 · Answer

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

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

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

shintaro-2 · Answer

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

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

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

ウィザードに従って
　カンマやタブなどの・・・　を選択
　区切り文字としてスペースを選択
　　と進んでいけば、
　
お望みのことはできると思います。、

last_shishi · Answer

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

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

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

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

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

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

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

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

「完了」で

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

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

こんばんは！

この回答への補足

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

No.3です。

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

この回答への補足

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

この回答への補足

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

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング