エクセルの関数で、記号などを抽出もしくは削除したい

エクセルの関数で、
・カタカナ
・ひらがな
・漢字
・アルファベット
・数字
上記以外を抽出または削除することは可能でしょうか。

＆、（）、’、'、”、←のような文字が含まれている場合エラーになる式があるので元のデータから削除したいのです。

約6万件と量が多いのと、特定の記号ではなく、種類もその数も随時変わる為
その都度SUBSTITUTEで指定するのが大変なのですが(置換機能も含む)
ｶﾀｶﾅ、ひらがな、漢字、アルファベット、数字のみ残すには
ほかに何かよい方法はないでしょうか。

質問者からの補足コメント

• 毎回消したい文字が変わります。
  特定の文字ではないので、現時点では↓以外の文字を探して置換しています。
  
  ｶﾀｶﾅ、カタカナ、ｱﾙﾌｧﾍﾞｯﾄ(ABC)、アルファベット(ＡＢＣ)、
  数字(123)、数字(１２３)、漢字、ひらがな
  
  以外の文字を抽出(抽出できれば削除も可能なので)、削除したいのですが
  何か良い方法がわかる方がいらっしゃいましたら、教えていただけないでしょうか。
  よろしくお願いいたします。

    補足日時：2017/10/13 14:43

• 後から追加で大変申し訳ないのですが
  ”アクティブセルのみ”に作用するよう変更できませんか。
  
  こちらの都合で本当にすみません!!

  No.3の回答に寄せられた補足コメントです。 補足日時：2017/10/14 08:28

No.6 ベストアンサー 回答者： tom04 回答日時： 2017/10/14 09:05

No.3です。

＞”アクティブセルのみ”に作用するよう変更できませんか。

↓に変更してみてください。

Sub Sample2()
Dim c As Range, myRng As Range
Set myRng = Selection
For Each c In Worksheets("Sheet2").Range("A1").CurrentRegion
If c <> "" Then
myRng.Replace what:=c, replacement:="", lookat:=xlPart
End If
Next c
MsgBox "完了"
End Sub

この回答へのお礼

連日お忙しい中ありがとうございます。
おかげさまで何とか次の段階に進むことができそうです。

しかし複雑なＶＢＡコードを書ける方というのは
私のような者からすると天才としか思えないのですが
学校とかに通ってその後何年も勉強してらっしゃるのか、
そもそもオツムの出来具合が違うのか、、、

下らない弱音を書いてお目汚ししてしまいすみません。
ありがとうございました。

お礼日時：2017/10/16 08:28

No.7 回答者： WindFaller 回答日時： 2017/10/15 00:09

こちらの質問には答えていただいていないので、こちらで、想像で作らせていただきました。

私の想像とは違うものなのか、それさえわからないけれども、言葉どおりに（抜けは存在するかもしれませんが）作れば、こうなるという見本のようなものです。これは、アクティブセルのみです。しょせん、納得いかないものを作ったものですから、返事は、つけてもつけなくても、どちらでもよいです。Unicodeに統一したかったのですが、できずじまいでした。数式は、当たり前ですが、「=」 がなくなります。

'//
Sub DeleteOthers()
Dim RegEx As Object
Dim Ms, m
Dim kata_h, kata_z, alpha_z, alpha_h, hira
Dim entTxt As String
Dim buf As String
Set RegEx = CreateObject("VBScript.RegExp")
With RegEx
　 'ｶﾀｶﾅ、カタカナ、ｱﾙﾌｧﾍﾞｯﾄ(ABC)、アルファベット(ＡＢＣ)、
　 '数字(123)、数字(１２３)、漢字、ひらがな
　
　　kata_h = Chr(&HA6) & "-" & Chr(&HDF) '半角カタカナ
　　kata_z = ChrW(&H30A1) & "-" & ChrW(&H30F6)　'全角カタカナ
　　alpha_z = Chr(&H8260) & "-" & Chr(&H829A)　'アルファベット全角
　　alpha_h = "A-Za-z"
　　hira = ChrW(&H3041) & "-" & ChrW(&H3093)　'ひらがな
　
　 .Global = True: .IgnoreCase = False: .MultiLine = True
　
　 .Pattern = "([^一-龠\d\０-９" & kata_h & _
　　kata_z & alpha_z & alpha_h & hira & "]+)"
End With
　If ActiveCell.HasFormula Then
　　entTxt = ActiveCell.Formula
　Else
　　entTxt = ActiveCell.Value
　End If
　Set Ms = RegEx.Execute(entTxt)
　buf = entTxt
　For Each m In Ms
　　buf = Replace(buf, m.Value, "")
　Next
　　ActiveCell.Value = buf
End Sub

この回答へのお礼

ご回答ありがとうございます。
今朝確認いたしましたもので質問にお答えせず、
大変申し訳ございませんでした。

当方の質問が説明不足でしたが、エラーになる式というのは
別ｼｰﾄにあるデータをINDIRECT関数で引っ張る式です。ただその素データは複数の人間が手打ちで入力する、式ではない固有名詞で、それ自体にカッコやらスラッシュやらハイフンやら様々よけいなモノが混じっております。(最初から入力するなというのは難しい…)

基本的にはちゃんと働く式なのですが、エラーが出る場合があり、その理由を調べたら、元データに&や’が含まれている時にエラーが出ることがわかりました。
そのため、現時点では頻度の高い、検討のつく記号は元データを事前に置換し、それでもエラーの出る場合、目視で探してさらに置換をする、という手順をふんでおりました。(元データの量が多いので、エラーの元となる記号がたとえ2.3個だったとしても探すのがきつい。)

なので、元データ(式ではない)の時点で余計な文字を一括で排除できれば、と思って質問させていただきました。

お礼日時：2017/10/16 09:25

No.5 回答者： WindFaller 回答日時： 2017/10/13 19:50

>エクセルの関数で、

>毎回消したい文字が変わります。

関数として成立していないから、それを置換で直すなんて、ちょっと考えられません。
それが、6万件とか、Excelの表に載せる前の段階で加工するなりすればよいのではなかったのですか？

具体例のパターンを出来る限り上げてほしいです。
自信のある人は、それを見ないで可能なのかもしれませんが、私は、そんなことは言えないですね。

文字でエラーになるのではなく、数式スタイル上でのエラーになっているのではないでしょうか。つまり、その対象は、数式でエラーを出しているという所のはずです。そして、数式を文字列として扱うわけです。

私は、おそらく、VBAマクロとしても特殊なスタイルで、単純な置換ではないと考えました。エラーが出る原因が、そういう文字が入るという単純な理由なのでしょうか。そんなことはあり得ないと思います。なぜ、そんな文字が入ったのでしょうか？

>＆、（）、’、'、”、←のような文字が含まれている場合エラーになる式
これらの文字が含まれているだけではエラーにはなりません。

「ｶﾀｶﾅ、カタカナ、ｱﾙﾌｧﾍﾞｯﾄ(ABC)、アルファベット(ＡＢＣ)、
数字(123)、数字(１２３)、漢字、ひらがな」

以下のこれらの文字を省いたら、エラーでない所もエラーになります。
=を始め　、+,-,*,/ ,(,)

数式が数式と成り立つような文字列にし直さなくてはならないはずです。
そんなにかんたんにできるとは思えません。

No.4 回答者： tatsu99 回答日時： 2017/10/13 19:17

マクロ（VBA）でなら、実現できますが、いかがなさいますか？

No.3 回答者： tom04 回答日時： 2017/10/13 18:50

こんばんは！

手っ取り早くVBAでの一例です。
元データはSheet1にあるとします。

まずSheet2に削除する文字列を羅列しておきます。
（薄い黄色が半角、その他が全角になります。）

※　注意点　※　
絶対に「？」と「＊」は入れないでください。
ワイルドカードになりますので、すべてのセルが空白になります。

標準モジュールです。

Sub Sample1()
Dim c As Range
For Each c In Worksheets("Sheet2").Range("A1").CurrentRegion
If c <> "" Then
Worksheets("Sheet1").Cells.Replace what:=c, replacement:="", lookat:=xlPart
End If
Next c
MsgBox "完了"
End Sub

※　画像はこちらが勝手に削除する文字を選んだだけです。m(_ _)m

No.2 回答者： mike_g 回答日時： 2017/10/13 13:53

》 カタカナ

》 ひらがな
》 漢字
》 アルファベット
》 数字
》 上記以外を抽出または削除する
ひらがな、漢字以外は半角と全角があるけど「上記」の意味は両方ともですか？

》 ＆、（）、’、'、”、←のような文字…削除したい
その中の「’」は半角文字「'」の全角文字なんだけど、分かってます？
他の３つは全角文字ということも？

No.1 回答者： 銀鱗 回答日時： 2017/10/13 13:37

＞その都度SUBSTITUTEで指定するのが大変なのですが(置換機能も含む)

いやいや、面倒なら「置換」しましょう。
たかだか十数文字でしょ。
十数回「置換」するだけで6万件分すべてを対象とした「置換」が終わるのです。
これって凄いことですよね。

…まさかエラーになるセルをエラーの都度一つずつ置換などで修正しているなんて落ちはありませんよね？


・・・余談・・・
個人的には半角カナは全角カナに直すべきと思うんだ。