【難しい】エクセルVBA

質問

エクセルVBAもしくは関数での回答希望です。

【条件】
A1～Aiには商品名、個数、単価、合計額が入っています。
A1～Aiに記載されてる各情報は半角スペースで区切られていたりいなかったりします。

D列にはA1～Aiで使われている商品名がランダムに全て入っています。

【行いたいこと】
A1~Aiに記載ある情報を、B１～Bi列には商品名をそれぞれ返す。C１～Ci列にはそれ以外を返すというようにしたいです。
つまり、二分割にするということです。
本当は商品、個数、単価、合計で4分割にしたいのですが複雑になりそうなので二分割でお願いします。

どうか方法を教えてください。

※参考として画像を載せます。あくまで画像は参考程度で、条件としては上記のものでお願いします。

回答No.11

kagakusuki

回答No.3、5、9,10です。

>できなかったものは
>ex)「かき (大型)」といった形のもの、そして「とうもろこし 500g」といったかたちのものです。

　商品名だけを示されても、元の文字列がどの様になっていたのかという事が判らなければ原因を調べる事は困難です。
　少なくとも、当方で「かき (大型)」と「とうもろこし 500g」を商品名とした仮のデータをそれぞれ作って試した際には正常に分離する事が出来ました。
　ですから、元のデータがどのようなものであったのかを御知らせ願います。

　それから、「かき (大型)」や「とうもろこし 500g」の話とは別件なのですが、回答No.10を投稿した後で、G2セルに入力す関数とH2セルに入力す関数にバグがあった事を発見しました。
　どの様なバグかと申しますと、例えば商品名が「セブン11iotoyokado11」で個数が11だった場合(単価はあまり関係しませんが例えば3円だった場合)、元データの文字列は

セブン11iotoyokado11 11 個　3 33

になりますが、回答No.10の関数ではこの元データから商品名と個数を分離する事が出来ません。
　そこで、H2セルの関数を次のものと差し換えて下さい。

=IF(COUNT($J3,$K3)=2,IFERROR(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2)," ",))))-1),SUMPRODUCT(MAX({1,2,3,4,5,6,7}*IFERROR((RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7})+0>=$K3/$J3-0.1)*(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7})+0<=$K3/$J3+0.1)*(LEFT(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J3,"#,##0")&TEXT($K3,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7}),1)<>"0"),0))))+0,""),"")

　同様にG2セルの関数も次のものと差し換えて下さい。

=IF($H2="","",TRIM(LEFT(INDEX($A:$A,ROW()),FIND(CHAR(1),SUBSTITUTE(INDEX($A:$A,ROW()),$I2,CHAR(1),(LEN(INDEX($A:$A,ROW()))-LEN(SUBSTITUTE(INDEX($A:$A,ROW()),$I2,)))/LEN($I2)))-LEN($H2)-7+SUMPRODUCT(MAX({5,4,3,2,1,0}*(IFERROR(RIGHT(LEFT(INDEX($A:$A,ROW()),FIND(CHAR(1),SUBSTITUTE(INDEX($A:$A,ROW()),$I2,CHAR(1),(LEN(INDEX($A:$A,ROW()))-LEN(SUBSTITUTE(INDEX($A:$A,ROW()),$I2,)))/LEN($I2)))-{2,3,4,5,6,7}),LEN($H2))+0,-$H2)=$H2))))))

　その上でまだ

>ex)「かき (大型)」といった形のもの、そして「とうもろこし 500g」といったかたちのもの

の場合に各項目ごとに分離出来ないようでしたら、その分離出来なかったという元の文字列がどの様なものであったのかをお知らせ願います。

回答No.10

kagakusuki

回答No.9の続きです。

　次に、H2セルに次の関数を入力して下さい。

=IF(COUNT($J2,$K2)=2,IFERROR(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1),SUMPRODUCT({1,2,3,4,5,6,7}*IFERROR((RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7})+0>=$K2/$J2-0.1)*(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7})+0<=$K2/$J2+0.1)*(LEFT(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1),{1,2,3,4,5,6,7}),1)<>"0"),0)))+0,""),"")

　次に、G2セルに次の関数を入力して下さい。

=IF($H2="","",IFERROR(TRIM(LEFT(INDEX($A:$A,ROW()),FIND(CHAR(1),SUBSTITUTE(INDEX($A:$A,ROW()),IF(MOD($H2,1)=0,TEXT($H2,"#,##0"),$H2),CHAR(1),(LEN(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1),IF(MOD($H2,1)=0,TEXT($H2,"#,##0"),$H2),)))/LEN(IF(MOD($H2,1)=0,TEXT($H2,"#,##0"),$H2))))-1)),""))

　次に、G2～K2のセル範囲をコピーして、G列～K列の3行目以下に貼り付けて下さい。

　以上です。

お礼

ありがとうございます。
入れてみた結果、大体の情報は分解することができました。

できなかったものは
ex)「かき (大型)」といった形のもの、そして「とうもろこし 500g」といったかたちのものです。

しかし驚きました、関数だけでここまでできるとは。質問の意図とはそれますが、どんな本を読んで底までの知識をつけたのでしょうか。

回答No.9

kagakusuki

回答No.3、5です。

>商品名ですが例えば「乾麺」や「小麦 200g」や「水1L」などのように数字を含むものも多々あります。
>また、「ABC」「ABC2」ということであれば例えば「乾麺」「乾麺 200g」という商品名もございます。

　それらの例の場合、商品名の末尾は「g」や「L」といった「数字ではない文字」となっておりますから、回答No.3で挙げた様な問題は発生しません。
　回答No.3で問題にしたのは、例えば「アリナミンA50」という商品名の様に末尾が数字(この場合は「50」)で終わっているものがD列に入力されていて、尚且つ、D列の別の行のセルには「アリナミンA50」の末尾から数字を取り除いた「アリナミンA」や「アリナミンA5」などといったパターンの商品名も存在していた場合の事なのです。

　尤も、回答No.5を投稿した後で良く良く考えてみましたところ、前提条件次第では回答No.3で挙げた問題を回避する事が可能となる方法を考え付きました。
　その上、その方法を使えば、D列に商品名を並べておかなくても商品名、数量、(数量の)単位、単価、合計額を分離して表示させる事が出来ますし、他にも以下の様な事が可能となります。

・数量の単位が「個」に統一されていなくても分離可能

・数量の単位が例えば「kg」や「インチ」などといった複数文字である場合でも分離可能

・数量の単位がある行では「個」となっていて、別の行では「kg」になっているといった具合に、異なる単位が混在していても分離可能

・例えば「アリナミンA50」の数量が「50個」になっているといった、数量の数と同じ数字が商品名の中に含まれてる場合であっても分離可能

・数量が例えば「30.48 cm」などの様に小数点以下の端数が含まれている場合であっても分離可能
　(但し、例えば「1,234.5g」などの様な「小数点以下の端数が含まれている数字」で、尚且つ「『,』を含んでいる数字表記」となっている場合には分離する事が出来ません)

・数量が例えば「1,234g」などの様に「3桁ごとに『,』が挟まっている数字表記」となっている場合であっても分離可能
　(但し、例えば「1,234.5g」などの様な「小数点以下の端数が含まれている数字」で、尚且つ「『,』を含んでいる数字表記」となっている場合には分離する事が出来ません)

・数量の単位と単価の間にはスペースが挟まっていてもいなくてもどちらでも構わない

　これらを可能とする方法を使うためには、以下の様な前提条件を全て満たしている必要があります。

・数量と単位の間には例えば「～ 個」といった具合に、数字と文字の間に必ず半角スペースがある

・単価と合計額の間には例えば「1,500 45,000」といった具合に、2つの金額の間に必ず半角スペースがある

・数量の数に単価を掛け合わせると、誤差0.1円の範囲内で必ず合計額の値となっていて、計算間違いのデータなどは存在していない

・単価と合計額はどちらも例えば「1,500 45,000」などの様に「3桁ごとに『,』が挟まっている数字表記」となっている(1000未満の場合は「,」が付いていなくても構わない)

・単価と合計額のどちらも整数となっているものばかりであり、単価や合計額が「小数点以下の端数が含まれている数字」となっているデータは無い

・数量が、例えば「1,234.5g」などの様な「小数点以下の端数が含まれている数字」で、尚且つ「『,』を含んでいる数字表記」となっている様なデータは無い

・数量、単価、合計金額は何れも半角数字(この場合の数字とは、半角の「,」や半角の「.」も含む)で入力されている

・単価の金額が999,999,999(円)を超えるものは無い
　(関数を書き換えれば最高金額の設定を変更する事も可能)

・数量が999,999を超えるものは無い
　(関数を書き換えれば最大値の設定を変更する事も可能)

　もし、上記の前提条件が全て満たされている場合には、以下の様にされると良いと思います。
　尚、御質問文では

>A1～Aiには

となっていましたが、各項目を分離して表の中に転記する場合、表の一番上の行には「商品名」、「個数」、「単価」、「合計額」などといった項目名を入力する必要がありますので、元データであるA列のデータはA1～AiではなくA2～Aiに並べられているものとします。
　また、

>B１～Bi列には商品名をそれぞれ返す。C１～Ci列にはそれ以外を返すというようにしたいです。

との事ですが、商品名、数量、(数量の)単位、単価、合計額の全ての項目を表示させるためには5列分の列が必要となり、

>D列にはA1～Aiで使われている商品名がランダムに全て入っています。

となっていて、D列は既に別の用途に使われている以上は、B列～F列の範囲に表を設ける事が出来ませんので、取りあえず仮の話として「商品名」はG2～Giのセル範囲に、「数量」はH2～Hiのセル範囲に、「(数量の)単位」はI2～Iiのセル範囲に、「単価」はJ2～Jiのセル範囲に、「合計額」はK2～Kiのセル範囲にそれぞれ表示させるものとします。

　まず、G1セルに「商品名」、H1セルに「数量」、I1セルに「単位」、J1セルに「単価」、K1セルに「合計額」とそれぞれ入力して下さい。
　次に、K2セルに次の関数を入力して下さい。

=IF(INDEX($A:$A,ROW())="","",IFERROR(REPLACE(TRIM(INDEX($A:$A,ROW())),1,FIND(CHAR(1),SUBSTITUTE(INDEX($A:$A,ROW())," ",CHAR(1),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(SUBSTITUTE(INDEX($A:$A,ROW())," ",)))),)+0,""))

　次に、J2セルに次の関数を入力して下さい。

=IF(ISNUMBER($K2),SUMPRODUCT(MAX(IFERROR(RIGHT(LEFT(TRIM(INDEX($A:$A,ROW())),FIND(CHAR(1),SUBSTITUTE(INDEX($A:$A,ROW())," ",CHAR(1),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(SUBSTITUTE(INDEX($A:$A,ROW())," ",))))),{1,2,3,4,5,6,7,8,9,10,11})*1,-9E+307))),"")

　次に、I2セルに次の関数を入力して下さい。

=IF(COUNT($J2,$K2)=2,IFERROR(TRIM(REPLACE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-1),1,FIND(CHAR(1),SUBSTITUTE(TRIM(INDEX($A:$A,ROW()))," ",CHAR(1),LEN(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2))-LEN(SUBSTITUTE(LEFT(TRIM(INDEX($A:$A,ROW())),LEN(TRIM(INDEX($A:$A,ROW())))-LEN(TEXT($J2,"#,##0")&TEXT($K2,"#,##0"))-2)," ",))))-1,)),""),"")

※このサイトの回答欄には入力可能な文字数に制限があり、これ以上続きを書き込む事が出来なくなったため、残りはまた後で回答する事に致します。

回答No.8

kkkkkm

D列の商品名一覧は最後の行まで商品名だとして、品名一覧を取り出し品名の長い順に並び替えたデータをもとに、A列のデータ内に品名を含むかを順に検索し、一度見つかった(分割したB列用のデータがすでにある)A列のデータは操作対象から外しながら順に検索しています。
ABC123がみつかったら除外されるので次にABCを含むで検索してもヒットしない。

Sub Example()
Dim i As Long, j As Long, LastRow As Long
Dim buf As Variant, ItemAry As Variant, DataStrAry As Variant
Dim BString() As String, CString() As String

With Sheets("Sheet1") '実際のシート名に変更してください
ItemAry = .Range(.Cells(1, "D"), .Cells(Rows.Count, "D").End(xlUp))
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
DataStrAry = .Range(.Cells(1, "A"), .Cells(LastRow, "A"))
ReDim BString(LastRow)
ReDim CString(LastRow)
'↓D列から取得した品名を名前の長い順に並び替えています
For i = LBound(ItemAry) To UBound(ItemAry)
For j = UBound(ItemAry) To i Step -1
If Len(ItemAry(i, 1)) < Len(ItemAry(j, 1)) Then
buf = ItemAry(i, 1)
ItemAry(i, 1) = ItemAry(j, 1)
ItemAry(j, 1) = buf
End If
Next j
Next i
For i = LBound(ItemAry) To UBound(ItemAry)
For j = LBound(DataStrAry) To UBound(DataStrAry)
If InStr(DataStrAry(j, 1), ItemAry(i, 1)) > 0 And BString(j - 1) = "" Then
BString(j - 1) = ItemAry(i, 1)
CString(j - 1) = Mid(DataStrAry(j, 1), InStr(DataStrAry(j, 1), ItemAry(i, 1)) + Len(ItemAry(i, 1)))
End If
Next j
Next i
.Range(.Cells(1, "B"), .Cells(LastRow, "B")) = WorksheetFunction.Transpose(BString)
.Range(.Cells(1, "C"), .Cells(LastRow, "C")) = WorksheetFunction.Transpose(CString)
End With
End Sub

回答No.7

mt2015

無理やりセル関数でやってみました。
D列には商品名が入っている事。その際、下にある商品名ほど優先とします。
＃商品名「あああ」「あああ2」がこの順番で登録されている場合、A列に「あああ2…」で始まる文字列があれば商品「あああ2」と判断
また、D1セルには該当商品なしの時に表示する文字列を入れます（例：「－」）。

今回、D1:D10に該当商品なし文字列＋商品名が入っているとします。

B1に↓の式を入れ、Ctrl+Shift+Enterで配列数式として確定。
=INDEX(D:D,MAX(ISNUMBER(FIND($D$1:$D$10,A1))*ROW($D$1:$D$10)),0)

C1に↓の式を入れます。
=TRIM(SUBSTITUTE(A1,B1,""))

使いにくいのでお勧めしません。
あくまでも参考と言う事で……

回答No.6

ushi2015

こんにちは、No4です。
他の方からも出ていますが、商品名に数字が入っているとダメですね。

回答No.5

kagakusuki

回答No.3です。

>D列にあるデータは商品名のみです。

　いえ、私が尋ねているのはそういう事では御座いません。
　回答No.3における例では、「ABC」という商品名は「アルファベットのみからなる商品名」であるのに対し、「ABC2」という商品名は「アルファベットと数字からなる商品名」であり、「A」から「2」までが商品名なのです。
　その様な「末尾が数字で終わっている商品名」の有無に関して尋ねているのであって、「D列に『"ABC"が2個ある事を示すデータ』が存在しているのか?」などと尋ねている訳では御座いませんので、その点をお間違え無き様に願います。
　質問者様が勘違いをされた事により、回答No.3の補足要求に対するお答えをまだ頂けておりません。
　質問者様には回答No.3を再読頂いて、そこに書かれている不明な点に関して御補則頂く様御願い致します。

補足

ご回答有難うございます。

意図を読み取れず申し訳ありませんでした。

商品名ですが例えば「乾麺」や「小麦 200g」や「水1L」などのように数字を含むものも多々あります。

また、「ABC」「ABC2」ということであれば例えば「乾麺」「乾麺 200g」という商品名もございます。

回答No.4

ushi2015

こんにちは
Sub test1()
　　Dim a As Range
　　Dim r As Range
　　Dim t As Range
　　Dim i As Long
　　Dim s As String
　　Dim b As Boolean
　　On Error Resume Next
　　Set a = Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(-2, 1)) _
　　　　　　　　.SpecialCells(xlCellTypeBlanks)
　　On Error GoTo 0
　　If a Is Nothing Then
　　　　MsgBox "B列に値有り。処理済み？"
　　　　Exit Sub
　　End If
　　Application.ScreenUpdating = False
　　For Each r In Range("D1", Range("D" & Rows.Count).End(xlUp))
　　　　For Each t In a
　　　　　　s = t.Offset(, -1).Value
　　　　　　b = False
　　　　　　For i = 1 To Len(s)
　　　　　　　　If Mid(s, i, 1) Like "#" Then
　　　　　　　　　　b = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　Next i
　　　　　　If b = True Then
　　　　　　　　If RTrim(Left(s, i - 1)) = r Then
　　　　　　　　　　t = r.Value
　　　　　　　　　　t.Offset(, 1).Value = _
　　　　　　　　　　　　Trim(Replace(s, t, ""))
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　Next

　　Application.ScreenUpdating = True
End Sub
これで、どうでしょうか？

回答No.3

kagakusuki

御質問文では説明されていない事柄で確認したい点があります。

　D列に並んでいる商品名の中に、例えば「ABC」という商品名があったとします。
　そして、同じくD列に並んでいる別の商品名の中に、例えば「ABC2」などといった類の「『別の行に記載されている商品名』+数字」というパターンに当てはまるような組み合わせのものは存在するのでしょうか？
　もしその様なパターンの商品名が存在していた場合において、A列に
ABC21個100 12
というデータがあった場合、そのデータは
「『ABC』という商品名の商品が21個ある事を示すデータ」
なのかそれとも
「『ABC2』という商品名の商品が1個ある事を示すデータ」
なのか、どちらであるのかをどの様な基準で区別しておられるのでしょうか？

　もしもその様なパターンが存在していて、尚且つ区別する基準が存在していない場合には、関数であれ、マクロであれ、"人間の手作業によるものであれ"、商品名と個数を完全に分離することは不可能になります。

補足

D列にあるデータは商品名のみです。
当社の全ての商品データを、今回やりやすいように同シート内に、私がD列にコピペしただけのもです。なので当然D列にはあるがA列にはないという商品も多々あります。

しかし、A列にあってD列にない商品は絶対にありません。

そして

>>ABC21個100 12
というデータがあった場合、そのデータは
「『ABC』という商品名の商品が21個ある事を示すデータ」

こちらの認識でお考えください。お手数をおかけします。

回答No.2

mt2015

商品名半角数字が無いという前提でセル関数でやります……
B1に=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)
C1に=TRIM(RIGHT(A1,LEN(A1)-LEN(B1)))

補足

商品名には「○○ 200g△個・・・」といったように数字が入る場合が多々あります。
また、例えば「乾麺 200g４個・・・」といったように商品名の中にスペースが入っているのも多々あり、商品名と個数の間にはスペースが無いというのも多々あります。

宜しくお願いいたします。