エクセルの関数SUBSTITUTEを、複数の条件を簡単に書く方法

やりたいことは、あるセルの「株式会社」、「（株）」、「(株)」、「㈱」を取り除きたいです。
そこで、以下のような関数を作ったのですが、とても見づらく、編集もしづらいです。
もっと簡単に書く方法ないでしょうか？

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K4,"株式会社",""),"（株）",""),"(株)",""),"㈱","")

取り除きたい文字列はこれからもっと増えていくので、このままだとどんどんと編集がし辛くなっていきそうだということを懸念しています。

No.6 ベストアンサー 回答者： fujillin 回答日時： 2020/07/28 12:43

こんにちは

＞もっと簡単に書く方法ないでしょうか？
もともとのエクセルの機能だけでは難しいようです。
機能を「削除」に限定して、ご質問の内容をユーザ定義関数化してみました。

使い方としては、ご提示の関数式に代えて
　=strDelete(K4,"株式会社","（株）","(株)","㈱")
といった感じで、元の文字列の後に、削除したい文字列を引数として列挙するものです。
削除単語群が、どこかのセル範囲に入っているような場合には、
　=strDelete(K4,削除文字のセル範囲)
という指定方法でもよいですし、これらが混在して、
　=strDelete(K4,"株式会社",削除文字のセル範囲)
のような指定になっていてもかまいません。


事前準備として、（ユーザ定義関数が使えるように）標準モジュールに以下のコードをコピペしておく必要があります。

Function strDelete(ByRef s As String, ParamArray words() As Variant) As String
Dim i As Integer, c As Range
Dim w As String, str As String
str = s
For i = LBound(words) To UBound(words)
　If TypeName(words(i)) = "Range" Then
　　For Each c In words(i)
　　　str = Replace(str, c.Text, "")
　　Next c
　Else
　　w = CStr(words(i))
　　str = Replace(str, w, "")
　End If
Next i
strDelete = str
End Function

この回答へのお礼

ありがとうございます。お教えいただいた関数をコピペして使用できるようになりました。
15年ぶりくらいにVBAのエディター画面開いたので、何をどうすればよいのか思い出せなくて手間取りましたが、無事動作しました。
理想の関数です。
ありがとうございました。

お礼日時：2020/07/29 15:16

No.5 回答者： 銀鱗 回答日時： 2020/07/28 11:33

配列数式で出来ないことは無いんですけど、

このケースでは、元のデータを「置換」機能で整理したほうが良いと思うんです。
目的を見失っているんじゃないのかなって印象がぬぐえないんですよ。

・・・本題・・・

作業列を設けて、
A列の元データに対して、それ以降の列の、
　1行目に検索する文字列。
　2行目に置換する文字列。
…を入力して、
　B列にSUBSTITUTE関数で1つ目の置き換え。
　C列にSUBSTITUTE関数で2つ目の置き換え。
　D列にSUBSTITUTE関数で3つ目の置き換え。
　E列にSUBSTITUTE関数で4つ目の置き換え。
…
のようにすれば良い。
それに、このように分けたほうが処理は早いし、見やすい。
見やすいという事はメンテナンス（追加）しやすいという事。
体裁が良くないなら最後の作業列だけを残して、他の作業列は非表示にすれば良い。


・・・余談・・・

これを配列数式でチョイチョイと作ることもできるけど、
数式の意味を理解していないと話にならないので今は配列数式は示しません。

この回答へのお礼

ありがとうございます。
たしかにその方法がシンプルですね。

お礼日時：2020/07/29 15:17

No.4 回答者： tom04 回答日時： 2020/07/28 10:58

こんにちは！

お望みの関数の方法ではなく、VBAで一気にやってみました。
K列全体を対象としています。

具体的な配置が判らないので、
「取り除きたい」文字列はA1セル以降、A列に羅列してあるとします。

Sub Sample1()
　Dim i As Long
　　For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
　　　Range("K:K").Replace what:=Cells(i, "A"), replacement:="", lookat:=xlPart
　　Next i
End Sub

とりあえずはお望みの結果になると思います。

※　元データに手を加えていますので、
別シートでマクロを試してみてください。m(_ _)m

この回答へのお礼

ありがとうごいます。
15年ぶりくらいにVBAのエディターを開いたので、手間取りましたが、お教えいただいたVBAで動作しました。
ありがとうございした。

お礼日時：2020/07/29 15:18

No.3 回答者： miu_chan 回答日時： 2020/07/28 10:38

No.2です。

すみません、質問の意図を間違えて解釈していました・・・すみません、無視してください。

この回答へのお礼

いえいえ、回答くださり、ありがとうございました。

お礼日時：2020/07/29 15:19

No.2 回答者： miu_chan 回答日時： 2020/07/28 10:36

どこかにリスト作れる場所があるなら、vlookupにしてはどうでしょう？

リストをA1:A10に作り削除したい項目を。B1:B10を空欄。（→A1:B10がリスト範囲）

vlookup(K4,A1:B10,2,false)

たくさん増えても、リスト範囲を広げて関数にそれを反映すればいいだけ。

No.1 回答者： 瀬戸の花婿 回答日時： 2020/07/28 10:09

とりあえず、わかっている分だけ「置換」機能で取り除いてからSUBSTITUTE関数を使ってみてはいかがでしょうか？

この回答へのお礼

回答ありがとうございます。
Ctrl + H で置換でもいいのですが、この作業が頻繁にあり、今後、取り除きたい文字列も変化していくので、できるだけ自動化でき、その仕組みを簡単に編集できればいいなと思い質問しました。
ありがとうございました。

お礼日時：2020/07/29 15:21