エクセル2010で数式を作りたいです

質問

Excel 2010を使用しております。
情報の並べ替えについて困っており、投稿させて頂きました。
どなたかお詳しい方、どうかお助けください。

セルA1に複数情報が並んでいるので、項目ごとに分けて別セルに並べ替えたいのですが、どのような式をつくったらきれいにまとめられるでしょうか。マクロとかはよく分からないので、数式で対応できたらと思います（カッコ内は例です）。

B1に店番・・・7桁もしくは9桁の数字 (121212121)
C2に店名・・・スペースを含む3文字から30文字の英字 (The World Game Tournament)
D1に日付・・・「2013-05-20」というフォーマットになっています
E1に時間・・・「08:40:09」というフォーマットになっています
F1に親番・・・3桁から5桁の数字(54321)
G1に組織名・・・スペースを含む3文字から40文字の英字 (ABC the first temple)
H1に機械番号・・・スペースを含まない8桁から10桁の数字(000123456)
I1に機械区分・・・スペースを含まない10文字の英字(EXAMPLEARE)

上記がすべてA1にスペースをはさんで並んでいて、100ちかくあります。
自分でやってみた式では桁数が違うせいか変なところで切れてしまったり…もうどうしたらいいものか、困っております。

勉強不足で申し訳ないのですが、どうかよろしくお願いいたします。

回答No.6

KURUMITO · ベストアンサー

Ａ１セルから下方にデータが有るとしてＢ１セルには次の式を入力して下方にドラッグコピーします。

=IF(ISNUMBER(LEFT(A1,9)*1),LEFT(A1,9),LEFT(A1,7))

Ｃ１セルには次の式を入力して下方にドラッグコピーします。

=LEFT(SUBSTITUTE(A1,B1,""),FIND("-",SUBSTITUTE(A1,B1,""))-5)

Ｄ１セルには次の式を入力して下方にドラッグコピーします。

=MID(A1,FIND("-",A1)-4,10)

Ｅ１セルには次の式を入力して下方にドラッグコピーします。

=MID(A1,FIND(":",A1)-2,8)

Ｆ１セルには次の式を入力して下方にドラッグコピーします。

=IF(ISNUMBER(MID(A1,FIND(":",A1)+6,7)*1),MID(A1,FIND(":",A1)+6,7),MID(A1,FIND(":",A1)+6,5))

Ｇ１セルには次の式入力して下方にドラッグコピーします。

=MID(A1,FIND(F1,A1)+6,LEN(A1)-(LEN(I1)+LEN(H1)))

Ｈ１セルには次の式を入力して下方にドラッグコピーします。

=IF(ISNUMBER(MID(A1,LEN(A1)-21,11)*1),MID(A1,LEN(A1)-21,11),IF(ISNUMBER(MID(A1,LEN(A1)-20,10)*1),MID(A1,LEN(A1)-20,10),MID(A1,LEN(A1)-19,9)))

Ｉ１セルには次の式を入力して下方にドラッグコピーします。

=RIGHT(A1,10)

お礼

お返事おくれて申し訳ございません。
勉強になりました。ご回答ありがとうございました。

回答No.5

noname#203218

下記式で可能かと思います。
文字列を空白置換を数式で実施している関係でB1～I1までの全ての式をコピペしないと正解は得られません。

B1=TRIM(LEFT(A1,FIND(" ",A1)))

C1=TRIM(LEFT(SUBSTITUTE(A1,B1,""),FIND("/",SUBSTITUTE(A1,B1,""))-6))

D1=TRIM(MID(A1,FIND("/",A1)-5,11))

E1=TRIM(MID(A1,FIND(":",A1)-2,8))

F1=TRIM(LEFT(TRIM(SUBSTITUTE(A1,B1&" "&C1& " "&D1&" "&E1,"")),FIND(" ",TRIM(SUBSTITUTE(A1,B1&" "&C1& " "&D1&" "&E1,"")))))

G1=TRIM(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1& " "&D1&" "&E1&" "&F1,""),I1,"")),FIND(" ",TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1& " "&D1&" "&E1&" "&F1,""),I1,"")),LEN(TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1& " "&D1&" "&E1&" "&F1,""),I1,"")))-10)))

H1=TRIM(SUBSTITUTE(SUBSTITUTE(A1,B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1,""),I1,""))

I1=RIGHT(A1,10)

お礼

お返事おくれて申し訳ございません。
勉強になりました。ご回答ありがとうございました。

補足

pidumさま、ありがとうございます。

さっそくやってみたのですが、BとEとI以外のところが#VALUEとなってしまいました。もしよろしければ解決方法をお教えいただけないでしょうか。

申し訳ございません、お時間があったらぜひお願いいたします。

回答No.4

m_and_dmp

NO.2です。
誤りがありました。訂正します。

正：
A12に　=RIGHT(A9,LEN(A9)-LEN(A11)-1)
結果は　000123456

誤：
A12に　=RIGHT(A9,LEN(A9)-LEN(A11))
結果は　 000123456
ゼロの前にスペースが出ます。

回答No.3

tom04

こんばんは！
VBAになってしまいますが、一例です。
尚、元データはA1セルからあるとします。

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

Sub Sample1() 'この行から
Dim i As Long, k As Long, str1 As String, str2 As String, str3 As String, myArray
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For k = 2 To Len(Cells(i, 1)) - 1
str1 = StrConv(Mid(Cells(i, 1), k - 1, 1), vbNarrow)
str2 = StrConv(Mid(Cells(i, 1), k, 1), vbNarrow)
str3 = StrConv(Mid(Cells(i, 1), k + 1, 1), vbNarrow)
If str2 = " " Then
If str1 Like "[0-9]" And str3 Like "[a-z A-Z]" Then
Cells(i, 1) = WorksheetFunction.Replace(Cells(i, 1), k, 1, "*")
ElseIf str1 Like "[a-z A-Z]" And str3 Like "[0-9]" Then
Cells(i, 1) = WorksheetFunction.Replace(Cells(i, 1), k, 1, "*")
ElseIf str1 Like "[0-9]" And str3 Like "[0-9]" Then
Cells(i, 1) = WorksheetFunction.Replace(Cells(i, 1), k, 1, "*")
End If
End If
Next k
Range("D:D").NumberFormatLocal = "yyyy-mm-dd"
Range("H:H").NumberFormatLocal = "@"
myArray = Split(Cells(i, 1), "*")
For k = 0 To UBound(myArray)
Cells(i, k + 2) = myArray(k)
Next k
Cells(i, 1) = Replace(Cells(i, 1), "*", " ")
Next i
Columns.AutoFit
End Sub 'この行まで

こんなんではどうでしょうか？m(_ _)m

お礼

お返事おくれて申し訳ございません。
勉強になりました。ご回答ありがとうございました。

回答No.2

m_and_dmp

A1に、
121212121 The World Game Tournament 2013-05-20 08:40:09 54321 ABC the first temple 000123456 ESAMPLEARE

A2に　=LEFT(A1,FIND(" ",A1,1)-1)
結果は　121212121　になります。

A3に =MID(A1,FIND(" ",A1,1)+1,100)
100というのは、多数という意味です。結果は、
The World Game Tournament 2013-05-20 08:40:09 54321 ABC the first temple ESAMPLEARE

A4に　=LEFT(A3,FIND("2",A3,1)-2)
結果は　The World Game Tournament

A5に　=MID(A3,FIND("2",A3,1),10)
結果は　2013-05-20

A6に　=MID(A3,FIND("2",A3,1)+11,8)
結果は　08:40:09

A7に　=MID(A3,FIND("2",A3,1)+20,5)
結果は　54321

A8に　=MID(A3,FIND("2",A3,1)+26,100)
結果は　ABC the first temple 000123456 ESAMPLEARE

A9に　=LEFT(A8,LEN(A8)-11)
結果は　ABC the first temple 000123456

A10に　=RIGHT(A9,11)
結果は　e 000123456

A11に、
=IF(LEFT(A10,1)=" ",LEFT(A9,LEN(A9)-11),IF(MID(A10,2,1)=" ",LEFT(A9,LEN(A9)-10),IF(MID(A10,3,1)=" ",LEFT(A9,LEN(A9)-9))))
結果は　ABC the first temple

A12に　=RIGHT(A9,LEN(A9)-LEN(A11))
結果は　 000123456

A13に　=RIGHT(A8,10)
結果は　ESAMPLEARE

A3 ,A8,A9,A10は補助式です。

並びは、計算のしやすさから、ご質問のA1, B1, C1, ... ではなく
A1, A2, A3, ... にしましたが、結果が出たら並べ替えは自由です。

お礼

お返事おくれて申し訳ございません。
勉強になりました。ご回答ありがとうございました。

回答No.1

Nouble

さぞや大変でしょう
式でも出来なくはないですけどね。
ＶＢＡか区切り位置でデータを分割することをお勧めします。

此方をご覧下さい
http://www.becoolusers.com/excel/text-to-columns.html

如何でしょうか？
お役に立てていたならば幸いです。

お礼

Noubleさま

すぐにご回答くださりありがとうございました。
区切りでとも思ったのですが、使用するPCによってはDataタブ自体がなかったりするので、数式で、と思い投稿しました。VBAというのは、すみません、私には上級すぎて…

データ分割で足りるところはそれで対応してみます。

ありがとうございました。