セル内の文字列に複雑な処理をしたい

質問

セルの中の複数の異なる文字列を以下のように処理したいのですが、Excelの標準の文字列操作の関数で試行錯誤してみたのですが、どうもうまくできませんでしたので、ご存知の方がいらっしゃいましたらご教授ください。VBAで処理しないとできないかもしれませんがよろしくお願いします。
あるリストのＣ列に備考欄が設けてあり、次のようなデータが入っています。
4/30 みかんを買った（強制改行して）
5/1 りんごを売った
5/2 ぶどうを食べた
5/5 すいかを買った
このセルを調べて、
(1)　セル内に"みかん"と"りんご"という文字列があったら、これを取り出して、右隣のセルに表示　　　　　　　　　　　(結果）⇒　みかん　りんご

(2)　セル内に"みかん"と"りんご"という文字列があったら、これを"Ａ","Ｂ"に置き換えて、右隣のセルに表示　　　　　　　　　　(結果）⇒　Ａ　Ｂ

(3)　セル内の数値データと"を食べた"、"を買った"、"を売った"を取り除いて、右隣のセルに表示　　　　　　　　　　(結果)　⇒　みかん　りんご　ぶどう　すいか

(4)　セル内に"か"を含む文字列があったら個数に関係なく"Ａ"に置換して右隣のセルに表示　　　　　　　　　　　　　(結果)　⇒　Ａ

すべて取り出したい文字列(上の例ではみかん、りんご）や置換したい文字列（Ａ、Ｂ)以外の文字列は全てクリアして表示しないようにします。少し複雑ですが、やり方をご存知の方がいらっしゃいましたら、お教えください。

回答No.2

kbonb · ベストアンサー

こんにちは

(1)セル内に"みかん"と"りんご"という文字列があったら、これを取り出して、右隣のセルに表示。「みかん」が複数出てくる場合、その数の分だけ「みかん」と表示。

=REPT("みかん　",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("りんご　",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))

(2)セル内に"みかん"と"りんご"という文字列があったら、これを"Ａ","Ｂ"に置き換えて、右隣のセルに表示。「みかん」が複数出てくる場合、その数の分だけ「Ａ」と表示。

=REPT("Ａ　",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("Ｂ　",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))

(3)セル内の数値データと"を食べた"、"を買った"、"を売った"を取り除いて、右隣のセルに表示

=REPT("みかん　",(LEN(C1)-LEN(SUBSTITUTE(C1,"みかん","")))/LEN("みかん"))
&REPT("りんご　",(LEN(C1)-LEN(SUBSTITUTE(C1,"りんご","")))/LEN("りんご"))
&REPT("ぶどう　",(LEN(C1)-LEN(SUBSTITUTE(C1,"ぶどう","")))/LEN("ぶどう"))
&REPT("すいか　",(LEN(C1)-LEN(SUBSTITUTE(C1,"すいか","")))/LEN("すいか"))

(4)セル内に"か"を含む文字列があったら個数に関係なく"Ａ"に置換して右隣のセルに表示

=IF(COUNTIF(C1,"*か*"),"Ａ","")

　以下のページもご参考になるのでは？

【エクセル技道場】－関数－セル内の単語数カウント
http://www2.odn.ne.jp/excel/waza/function.html#SEC12

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html#SEC12

お礼

janmaさん、ご回答ありがとうございます。関数だけでもこのような処理ができるとは、本当に勉強になりました。janmaさんがご教授くださった関数を解析しようと関数を調べまくりました。その間何度「なるほど～～」と発したことでしょう!!こういう関数の組合せはよく使われる手法なのでしょうか?それともjanmaさんの豊富なご経験からすっと思い描かれるものなのでしょうか？いずれにしても、いろいろ困難なことに突き当たるたびに得るものは大きいなぁと重い知らされました。これからも、1つ1つ解決して知識を身に付けていきたいと思います。本当にありがとうございました。今後もまた何かありましたら、ご教授を賜りたいと思いますので、よろしくお願いします。

回答No.5

april21

>april21さんはACCESSは使っていらっしゃいますか？もし使用されているのであれば、このような処理はACCESSでもできるのでしょうか？
殆ど統合ソフトで事足りるので触った程度です。

データベースですから条件を指定して抽出してレポートにすれば良いのでは？

＞データが大量なので、やはりデータベース専門のソフトで管理し、必要なデータだけ取り出して、処理する方がいいのでは？
ACCESSでデータベースを作るほどのものなのかはお仕事の内容もわかりませんし
判断できません。

中長期的にデータを集めてそれをベースに必要な情報（条件に該当する）を集める
というのがACCESSに向いてると思います。
（テーブルとか色々決めないといけないので、形をある程度決める必要がある）
そこまでしなくてもその場その場で処理できれば良いのであればexcel。
（形を特に決める必要はない）

私が思ってるだけですから間違っててもお許しを。（私は一般人(^^ゞ）

データベースの方で質問された方が良いのでは？

お礼

april21さん、ご回答ありがとうございます。おっしゃるとおり、データベースはテーブルの設計がとても重要だそうですね。将来的にデータをどのような条件で処理するのかはっきりした形が決まってからの方がよさそうですね。当面Excelでがんばってみようと思います。度々厄介なご相談をしてしまうかもしれませんが、気が向いたときなど、どうぞお力をお貸しいただければ嬉しく思います。ところで、april21さんは本当に一般人なのでしょうか？？？実は凄腕のプロだったりするのでは？？？（^_^;）今回も早々の御返事本当にありがとうございました。

回答No.4

april21

（3）が答えになってなかったので追加
ARC様の関数を使わせて頂いて
Ｅ列の条件1に「*買った*」、関数をＧ列に（右に表示するので関数のＣ1の部分は
列はＦで「*買った*」と同じ行に変更（Ｅ4に「*買った*」ならＦ4に

お礼

april21さん、いつも心行き届いたご回答ありがとうございます。これで文字列操作に関する点で、私たちの業務に必要なことはほとんど完璧に対応できます。本当に助かりました。ところで、april21さんはACCESSは使っていらっしゃいますか？もし使用されているのであれば、このような処理はACCESSでもできるのでしょうか？というのもデータが大量なので、やはりデータベース専門のソフトで管理し、必要なデータだけ取り出して、処理する方がいいのでは？という案が出ているのです。もし何かアドバイスなどありましたら、また何かの機会にでもご意見を聞かせていただければ…と思います。今回はどうもありがとうございました。

回答No.3

april21

解決されたようなので
前回、書いたデータ処理のＶＢＡを使って
（1）条件1に「*みかん*」　条件2に「*りんご*」Ｃ列だから上のセル
　　の両方に3を入力するデータ「みかん　りんご」
（2）↑と同じで、入力するデータ「Ａ　Ｂ」
（3）は「編集」-「検索」で"を食べた"、"を買った"、"を売った"をそれぞれ
　　検索して置き換えの文字に何も入力しないで「置換」をクリック。
　　（これだと元のセルが変えられちゃうけど・・・何かの時にお役立て下さい^_^;）
（4）条件1に「*か*」Ｃ列だから上のセルに3を条件2の方は0に
　　入力するデータ「Ａ」

いちおう、参加してみました。m(__)m（確かめてません）

お礼

april21さん、ご回答ありがとうございます。前回april21さんに作っていただいたプログラムのおかげで仕事が予定よりずっと早く進んでおります。あのプログラムをそれぞれ処理するリストに応じて部分的に書換えて使わせて頂いておりますが、まさかこの文字列処理にも使えるなんて!!でも確かにそうですね。ただ文字列を"取り出す”とか"置換"するという処理的な部分ばかりにしか目が向いていませんでした。早速プログラムを書換えて実行してみました。大成功でした。本当にありがとうございます。april21さんに作っていただいたＶＢＡは、頭を柔らかくして違った角度から検証してみれば、もっといろいろな処理をするのに使用できそうですね。私の"とっておき"のＶＢＡとしてずっと大事に（？）していきます。私の上司はすっかり気をよくしてデータ処理の仕事を私どもにばかりまわしてくるようになってしまったので、これからもExcelでの仕事はどんどん増えそうです。また困難にぶつかると思われますので、ご迷惑だとは思いますが、どうぞお力をお貸しくださいませ。今回もまたまた丁寧なご教授ありがとうございました。

回答No.1

ARC

(1)
=TRIM(IF(ISERROR(FIND("みかん",C1)),"","みかん ") & IF(ISERROR(FIND("りんご",C1)),"","りんご "))
※ただし、「みかん」が複数出てくる場合でも、１回しか「みかん」と表示されません。
「みかん」の数だけ「みかん」と表示したい場合は、VBA使うしかないと思います。

(2)
=TRIM(IF(ISERROR(FIND("みかん",C1)),"","A ") & IF(ISERROR(FIND("りんご",C1)),"","B "))
※(1)の変形であるため、検索対象が複数存在する場合は(1)と同じになります。

(3)
SUBSTITUTE関数が使えそうなのですが、Excelの仕様で、関数は最大７つまでしかネストできないみたいなので、０→"" , １→"",…と置換していっても、７まで置換した時点でエラーになります。

VBAで数字を削除する関数「NumKill()」をあらかじめ作成しておけば、以下のような感じで書けると思います。
=TRIM(NumKill(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C1,"*1
"," "),"/",""),"を買った",""),"を食べた",""),"を売った","")))
※*1の位置には改行(Alt + Enter)を入れて下さい。

Public Function NumKill(SrcString As String) As String
　Dim i As Long
　NumKill = SrcString
　For i = 0 To 9
　　NumKill = Replace(NumKill, CStr(i), "", , , vbTextCompare)
　Next i
End Function

(4)
=IF(ISERROR(FIND("か",C1)),"","A")

お礼

ＡＲＣさん、早々のご回答ありがとうございます。以前もお力を貸していただきましたね。こちらはなかなか進歩せずで恥ずかしい限りです。本題のほうですが、このようにいくつかの関数を組み合わせれば、ＶＢＡだけに頼らずとも複雑な処理がある程度はできるのですね。大変勉強になりました。早速やってみましたが見事に要望とおりの処理が実行できました。大変助かります。さらに数字を削除するＶＢＡまでご教授くださり恐縮です。このＶＢＡはまた別のリストの処理に使用できると思います。実際にあるのです。商品番号に「DoCoMo1234」というようなデータがあって、この数字だけを一気に取りたいというようなことが・・・。本当にありがとうございました。