エクセルのVBAコードと数式について教えてください。

B86に様々な物件が表示され、その物件名に様々なナンバー文字が含まれている場合に
「№」と置き換えが出来るように設定しました。
様々なナンバーのリストをＫ77:Ｋ136に設定してます。
セルＢ88に、数式「=ReplaceNumbers(B86,K77:K136)」を設定し
末尾の文字を削除出来るように設定してますが、
例えば
B86に「北海太郎No＿1」と表示された場合に
B88に「北海太郎№＿1」と表示されてしまします。
この表示を「北海太郎№1」と出来る方法を教えてください。

作業ブックの標準モジュールコード
Function ReplaceNumbers(target As String, numbers As Range) As String
Dim cell As Range
Dim result As String
Dim num As String

result = target

For Each cell In numbers
num = cell.Value
' 末尾の記号を削除
num = RemoveTrailingSymbols(num)
result = Replace(result, cell.Value, "№")
Next cell

ReplaceNumbers = result
End Function

Function RemoveTrailingSymbols(str As String) As String
Dim symbols As String
Dim i As Long

symbols = "．．、、,。＿_：:－－" ' 削除対象の記号を定義

For i = Len(str) To 1 Step -1
If InStr(symbols, Mid(str, i, 1)) = 0 Then
RemoveTrailingSymbols = Left(str, i)
Exit Function
End If
Next i

RemoveTrailingSymbols = "" ' すべて記号の場合
End Function
以上となります。
よろしくお願いします。

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

• ご連絡ありがとうございます。
  リストの画像をお送りします。
  リストの上から7つ目の
  「No＿」をコピーして、B86に「北海太郎No＿1」と記載をした場合に
  B88に「北海太郎№＿1」と表示されてしまします。
  よろしくお願いします。

  
  No.1の回答に寄せられた補足コメントです。 補足日時：2025/03/19 12:30

• 

  回答、ありがとうございます。
  このリストを使用して上手く置き換えが出来るコードに須つ方法はありますか。
  教えて頂けますか。
  よろしくお願いします。

  No.2の回答に寄せられた補足コメントです。 補足日時：2025/03/19 12:56

No.5 ベストアンサー 回答者： tatsumaru77 回答日時： 2025/03/20 18:20

No4です。

もし、手作業で文字数の多い順に並べ替えるのでなく、関数で文字数の多い順に並べ替えたいのなら、そのようにすることは可能です。
その場合は、
L列に文字数（L77～L136）
M列に並べ変えたリスト(M77～M136)
N列に並べ変えたリストの文字数(N77～N136)
が表示されます。

そして、B88セルには
数式「=ReplaceNumbers(B86,K77:K136)」ではなく
数式「=ReplaceNumbers(B86,M77:M136)」
を設定する必要があります。

もし、そのようにしたいなら、その旨補足してください。

この回答へのお礼

ありがとうございます。
早速試してみます。

お礼日時：2025/03/20 18:50

No.4 回答者： tatsumaru77 回答日時： 2025/03/20 12:08

No3の方の指摘通りです。

もし、現状の仕様のまま、期待した結果を得たいなら、K7:K136の範囲を
文字数の多い順に並べ変える必要があります。
多分、このリストは、一度登録すれば、あまり修正は発生しないので、手作業で良いかと思います。
以下、その手順です。
１．K7:K136の範囲を選択しコピーします。
２．それを別の任意の作業用シートのA1に貼り付けます。そうするとA1～A130にリストが表示されます。
３．B1に 以下の式を入力します。
=LEN(A1)
４．オートフィルで、B130までコピーします。
５．B列を選択し、並べ替えとフィルターで降順を指定します。
「選択範囲を拡張する」を選択して実行します。
６．文字数数の多い順に並べられたリストができたので、A1～A136をコピーし、
元のシートのK7:K136に貼り付けます。

以上で完了です。作業用シートは削除してください。
添付資料は、作業用シートの内容です。
左側が、並べ替え前、右側が並べ替え後です。

No.3 回答者： fujillin 回答日時： 2025/03/19 13:37

No2です。

補足にご提示のリストは画像なので、よく見えませんし文字コードも確認できませんけれど、「No＿」という項目があれば変換はされるはずです。

ただし、ご提示の関数内の処理はリスト順に処理されるような仕様になっていますので、包含関係にあるような文字列（例えば、「No」と「No＿」）がそのような順でリストに記されていると「No」で先に置き換えられて、「No＿」の置き換えは行われないという事象が発生します。
ご提示のリストはよく見えませんけれど、そうなっていそうな雰囲気ですので、そちらが原因かもしれません。

対処法としては、包含している方を先にリストに記すようにすればとりあえずの対応は可能と思います。
そんな面倒なことは気にしなくてもよい様にしたければ、関数側でそのように順を読み替えて処理するように対処すれば良いでしょう。
そのように変更なさりたければ、発注先にそのように修正依頼をなさってください。
あるいは、No2に記した「おかしな点（＝無駄な処理）」が、何かそれに関係しているのかも知れませんけれど、ご提示の内容では無駄な処理になっているので、当方には意図がわかりかねます。

この回答へのお礼

何時も回答ありがとうございます。
貴者のアドバイスを参考にもう少し考えてみます。

お礼日時：2025/03/19 14:13

No.2 回答者： fujillin 回答日時： 2025/03/19 12:52

こんにちは

どのような仕様で作成されている関数なのかが不明ですが、ご提示のコードから見る限り、第二引数で指定されたリスト内にある文字列を「№」に置き換えるだけのものです。
ですので、指定リストに変更したいパターンが記されていないだけではないでしょうか？？

なお、ご質問には直接関係ありませんけれど、ご提示のコードだけから見ると　RemoveTrailingSymbols()の関数は、処理内容的には意味を持っていませんので、無駄な処理を行っているように見えます。
わざわざ無駄な処理を加えるとも思えないので、そのあたりが「発注時の仕様」とコードの食い違いなのかも知れません。
発注先に再確認なさった方が宜しいかも知れません。

No.1 回答者： tatsumaru77 回答日時： 2025/03/19 12:19

Ｋ77:Ｋ136には、どのような値が設定されtますか。

B86に「北海太郎No＿1」と表示された場合に
B88に「北海太郎№＿1」と表示されるようなケースを提示してください。

Ｋ77:Ｋ136になにも設定しないで、実行するとB88には、
「北海太郎No＿1」とひょうじされます。