1列のデータを１行に変換し規則性のない文字列削除

質問

エクセル初心者です。

どなたか、助けてください。

下記のように、１列のひとつのセルに入っているデータを複数ファイルあり

A　：●●
B　：▲
C　：■■

●●　▲　■■

といった
不要で規則性のないデータを削除して
必要な情報（これも規則性がないです）
をひとつのファイルのひとつの特定のセルの中に1つづつ行に並べかえたいのです。

いろいろ調べてみて、2列のものなら、行列変換という形で、できるところまでわかって
１列

A　：●● 、1
B　：▲ 、1
C　：■■、1

として、
A　：●● ,B　：▲ , C　：■■
１　　　　　、１　　　　、１

に並べ替えられたのですが、
(1)複数のファイルをひとつにして
(2)規則性のない不要なデータを削除して
(3)特定のセルに入れたい

のですが、
全く知識がないので、何か式を書いたり、プログラムも書いたことがないので
困っています。

どなたか、サルにでもわかるように、
ヒント、知識、お力を貸してください。

お願いします。

回答No.4

queuerev2 · ベストアンサー

A, B, C の中に「：」がある場合を解説いたします。
VBAは全く使わないとします。
前回同様、No2の解答の「=LEFT(A1,3)」のところをどのように変更したらよいか、つまりB1に入力する数式を説明いたします。

１．　A, B, C の中に「：」はあるが「　：」はない
「：」の１文字だと複数個あるが、たとえば「　：」のように連続する複数文字のパターンは１個しかないなら、それを目印に区切る。
ただし区切りが２文字以上になるのでその対応が必要。
セル１つでもできなくはないのですが、区切り文字を入れるセルを別途確保することとする。
ここでは区切り文字を入れるセルをG2とする。
1) G2に「'　：」を入力　→　G2の値としては「　：」になる
2) B1に入力する数式は以下の通り
「A　：」を得るには「=LEFT(A1,FIND($G$2,A1)+LEN($G$2)-1)」、
「●●」を得るには「=MID(A1,FIND($G$2,A1)+LEN($G$2),LEN(A1))」とする。

２．　A, B, C の中にある「：」の個数が決まっている
たとえば、A, B, C の中に「：」がそれぞれ２個ずつ入っているとし、そこでは区切らず左から３個目の「：」で区切るとする。
この場合、別途セルを３個確保する。ここではG2～G4を使用することとする。
また、処理するセル１個について式を入力するセルは２個使用する。ここではB1とC1を使用することとする。
1) G2に「：」を、G3に「＊」を、G4に「3」を入力
（H2はG2とは異なる文字を１文字入力しておく。ここでは「＊」とした。もし区切り文字を「＊」にするならH2は異なる文字にすること。）
（I2は区切り文字の「：」が左から数えて何個目の「：」かを入力すること。）
2) B1に数式「=FIND($G$2&$G$2,SUBSTITUTE(SUBSTITUTE(A1,$G$2,$G$3&$G$2),$G$2,$G$2&$G$2,$G$4))-LEN($G$3)*$G$4」を入力
3) C1には以下の通り数式を入力
「A　：」を得るには「=LEFT(A1,B1+LEN($G$2)-1)」
「●●」を得るには「=MID(A1,B1+LEN($G$2),LEN(A1))」

３．　A, B, C の中にある「：」の個数は不定だが「●●」「▲」「■■」の中に「：」はない
この場合は最も右の「：」で区切る。
別途確保するセルは２個。ここではG2, G3を使用することとする。
また、処理するセル１個について式を入力するセルは３個使用する。ここではB1～D1を使用することとする。
1) G2に「：」を、G3に「＊」を入力
（G2, G3については上記２．と同じ）
2) B1に数式「=(LEN(A1)-LEN(SUBSTITUTE(A1,$G$2,"")))/LEN($G$2)」を入力
3) C1に数式「=FIND($G$2&$G$2,SUBSTITUTE(SUBSTITUTE(A1,$G$2,$G$3&$G$2),$G$2,$G$2&$G$2,B1))-LEN($G$3)*B1」を入力
4) D1には以下の通り数式を入力
「A　：」を得るには「=LEFT(A1,C1+LEN($G$2)-1)」
「●●」を得るには「=MID(A1,C1+LEN($G$2),LEN(A1))」

４．「：」がどこに何個あるかわからない
数式やマクロで一括処理するには別の規則が必要。

上記１．～３．の方法でA列の複数のデータを処理する場合、B列（～D列）をA列のデータにあわせてコピーペーストするのはよいとして、
G2（～G4）をコピーする必要はありません。

数式の解説は長くなりますので申し訳ありませんがとりあえず割愛させていただきます。

なお、上記の数式が複雑すぎるなどの場合はユーザー定義関数やマクロを使う方法がありますが、どちらもVBAを使用することになります。

お礼

ありがとうございます。

助かりました。

腱鞘炎から抜け出せそうです。

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

少しづつではありますが、精進していきたいと思います。

回答No.3

queuerev2

A1に「A　：●●」が入力されているとして、「：」（全角コロン）のところで区切るためにはNo.2の解答の「=LEFT(A1,3)」のところをどのように変更したらよいかを説明いたします。
「A　：」を得るには「=LEFT(A1,FIND("：",A1))」に、
「●●」を得るには「=MID(A1,FIND("：",A1)+1,LEN(A1))」に変えてください。
ただし、「：」が、A, B, C の中にはなく区切り位置には存在するという前提です。

関数「FIND」ですが、第１引数（"："）を第２引数(A1)の中から探し、最も左にあるものの位置を返す関数です。
この場合左から３番目にありますので、「FIND("：",A1)」は「3」になります。
「LEFT」は第１引数(A1)の左から、第２引数（FIND関数の結果の「3」）で指定した文字数を取り出す関数です。

「LEN」は文字列の長さを返す関数です。「A　：●●」は５文字なので、「LEN(A1)」は「5」になります。
「MID」は第１引数(A1)から部分文字列を取り出します。
取り出しの開始位置は第２引数「FIND("：",A1)+1」、終了位置は第３引数「LEN(A1)」です。
第２引数の値ですが、FIND("：",A1)+1 → 3+1 → 4 となります。
結局、MID(A1,FIND("：",A1)+1,LEN(A1))は「A　：●●」の４文字目から５文字目までを取り出し、「●●」となります。

「：」がない場合、上記の式では「#VALUE!」というエラーになります。
エラーを出さないようにするには、たとえば
=IF(ISERR(FIND("：",A1)),A1,LEFT(A1,FIND("：",A1)))
のようにしてください。

A, B, C の中に「：」がある場合はさらにややこしくなりますが解説ご希望であればお知らせください。

お礼

ありがとうございます。

補足

返信が遅くなりましてすみません。
回答ありがとうございます。

＞A, B, C の中に「：」がある場合はさらにややこしくなりますが解説ご希望で＞ればお知らせください。

ご指摘の通り、
A, B, C の中に「：」あります。

その場合の方法を
何卒、ご教授ください。

回答No.2

queuerev2

不要なデータにも必要な情報にも何も規則性がないのでしょうか？
もし本当に何も規則性がないなら、１つずつ目視で判別するしかないですよね。

たとえば上記の例で「A　：●●」「B　：▲」「C　：■■」から「●●」「▲」「■■」を削除することを考えてみます。
仮に上記データはA1～A3に入っていて、B列以降は未使用とします。
規則は何でしょうか？　左から３文字目までを残せばよい？　左から「：」（全角コロン）までを残せばよい？　それとも別の規則？
とりあえず左から３文字目までという規則でよいとします。
そうであれば、B1に「=LEFT(A1,3)」という数式を入力します。（「式を書いたこともない」とのことですが、数式の入力方法はご存知でしょうか？）
すると、B1に「A　：」が表示されます。
次にB1をコピーしてB2～B3に貼り付けます。これでB2に「B　：」が、B3に「C　：」が表示されます。
このまま使用してもよいのですが、元の不要なデータを含むセルを削除したい場合や「A　：」などをさらに書き換えたりする場合は、ここから値のみをコピーペースとします。
値のみのコピーペーストの手順は、
B1～B3を選択、コピー、C1を選択、形式を選択して貼り付け→値をクリック→OKをクリック
です。
ここでA列～B列を削除すれば、手動で「●●」「▲」「■■」を削除した場合と同じデータが得られたということになります。
これを1000回繰り返すのはちょっと大変かもしれません。そうなるとマクロ(VBA)でしょうか。

いずれにしろ、元データの規則性とやりたい処理をもう少し詳しく示していただければさらにお答えできると思います。

お礼

きっかけになるヒントを頂き
誠に、ありがとうございます。

補足

丁寧な、ご指導ありがとうございます。

式のLEFTを教えて頂きありがとうございます。

私の質問の仕方が無学なため、
もう一度ご指導を仰ぎたいのですが、

(1)A、B、Cは、バラバラの長さで、
(2)：以降を抜き出したいのですが、これもバラバラの長さになってます。

でそのような場合は、どんな式もしくは、関数と呼ばれるものが
あるのでしょうか？

何卒、お力をお貸しください。

お願いいたします。

回答No.1

-9L9-

知識がないなら全部手作業でやりゃいいじゃん。無知なまま下手に関数やVBAを使ってデータをめちゃくちゃにしたら取り返しがつきません。

お礼

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

300ファイル以上を昨晩から手作業でやっているのですが、
あと1000ファイルあるもので、、。

どなたが、ヒントだけででも、お力をお貸しください。