Excelの数式について教えてください。

Question

下記の数式は以前教えて頂いた数式で、
指定セル値「受付!F2」の表示の一部に{"NO.","NO","No.","No"}があった場合に
（№）に表示を変更出来る数式になっております。
しかし、「受付!F2」の表示の一部のナンバー表示が{"NO.","NO","No.","No"}以外の時もあり、
その場合は（№）に表示を変更出来ません。
{"NO.","NO","No.","No"}部分が色々なケース（ナンバー表示）に対応できる方法があれば、教えてください。
現状の数式
「=REDUCE(受付!F2,{"NO.","NO","No.","No"},LAMBDA(x,y,SUBSTITUTE(x,y,"№")))」
以上となります。
よろしくお願いいたします。

goomania · Accepted Answer

ご質問者の補足コメントに対し、No.1さんからの追加回答が付かないようです。
No.1さんの回答内で、正規表現を用いる方法が解説されているものの、VBAでは使えない表現が登場している気がします(大変失礼ですが、No.1さんの回答を見て生成AIの回答を想起してしまいました)。
そこで、失礼を承知のうえで、横ヤリになってしまいますが、その部分を修正したものを以下にお示しします。

Function ReplaceNumberFormat(inputText As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
inputText = StrConv(inputText, vbNarrow)
regex.Pattern = "(no[\.:]?|number[\.:]?)"
regex.Global = True
regex.IgnoreCase = True
ReplaceNumberFormat = regex.Replace(inputText, "№")
End Function

上記のコードは正規表現による置換前の文字列と置換後の文字列をコード内に記載していますが、関数を呼び出す都度指定する(可変にする)こともできます。

Function ReplaceNumberFormat2(inputText As String, ByVal Pattern As String, ByVal ReplaceWith As String) As String
Dim regex As Object
Set regex = CreateObject("VBScript.RegExp")
inputText = StrConv(inputText, vbNarrow)
regex.Pattern = Pattern
regex.IgnoreCase = True
regex.Global = True
ReplaceNumberFormat2 = regex.Replace(inputText, ReplaceWith)
End Function

しかし、No.1さんのバターンにあるnumberという語句は"NO.","NO","No.","No","Ｎｏ.","ＮＯ","no.","no"の例示からすると違和感があります。
また、「受付!F2」セルに"NO.","NO","No.","No","Ｎｏ.","ＮＯ","no.","no"のどれも無く、「No.」をタイプミスして「Mo.」となっていたり、「#」だったり、さらに#入力もれで「数字だけ」だったりすると上記のコードでは役に立ちません。
そこで、究極のお節介としては、「数字の前に"№"を強制付与する」という考えもあります。
その場合、上段のコードを若干修正して、以下のようなコードにすればよいと思います。

Function ConvertToNumber(ByVal inputText As String) As String
Dim regex As Object
Dim matches As Object
Dim str As String
Dim result As String

Set regex = CreateObject("VBScript.RegExp")
inputText = StrConv(inputText, vbNarrow)
regex.Pattern = "(no|#|mo)[\.:]?"
regex.Global = True
regex.IgnoreCase = True
result = regex.Replace(inputText, "№")
regex.Pattern = "\d+"
Set matches = regex.Execute(result)
If matches.Count > 0 And InStr(result, "№") = 0 Then
str = Left(result, InStr(result, matches(0)) - 1)
If str <> "" Then
result = Replace(result, str, str & "№", 1, 1)
Else
result = "№" & result
End If
End If
ConvertToNumber = result
End Function

上記コードは置換すべき文字列を"№"に置換するとともに、置換すべき文字列が存在しないときは最初の数字を探して、その前に強制的に"№"を付与するものです。
従って「北海太郎1234」は「北海太郎№1234」に置換され、「東神楽町南1番通no1234」は「東神楽町南1番通№1234」に置換されます。
しかし、上記コードは「お節介コード」なので余計なお節介が働き「東神楽町南1番通1234」は「東神楽町南№1番通1234」に置換されてしまいます。

いずれのコードも標準モジュールに貼り付けてから、結果を表示したいセルに
=ReplaceNumberFormat(受付!F2)
=ConvertToNumber(受付!F2)
などと記述すれば使えます。

正規表現による置換パターンを都度指定するReplaceNumberFormat2の場合、パターンの指定は自由に変更できるので、「#」パターンや「Mo」パターンも自由に追加指定できます。
=ReplaceNumberFormat2(受付!F2,"(no|#|mo)[\.:]?","№")
結果を表示したいセルに、上記のように記述すればよいだけです。

お好みでご利用ください。

※老婆心ですが、環境依存文字である"№"に置換するという方針が、将来展望も含めてシステムとして好ましいのかは疑問が残ります。

fujillin · Answer

こんばんは

＞ナンバー表示が{"NO.","NO","No.","No"}以外の時もあり、
＞その場合は（№）に表示を変更出来ません。
許容する範囲が明示されていませんけれど、何があっても「№」と表示したいのなら、そのセルに
　=IF(受付!F2="","","№")
という式でも設定しておけば、必ず「№」と表示されます。

対象とする範囲をきちんと限定できるのなら、ご提示の式で対象範囲を広げれば良いでしょう。
多いようなら、リストにしてそちらを参照するようにすれば宜しいかと。

amerikaumare · Answer

現状の数式では、特定のナンバー表示（{"NO.","NO","No.","No"}）にしか対応していないため、その他の形式には対応できません。この問題を解決するには、以下のアプローチを考えられます。

1. より広い条件で置換する方法
正規表現を用いると、様々な「ナンバー表示」に対応できますが、Excelの標準関数では正規表現を直接扱えません。そのため、以下のように複数のパターンを手動で列挙する必要があります。

改良した数式例：
=REDUCE(受付!F2,{"NO.","NO","No.","No","Ｎｏ.","ＮＯ","no.","no"},LAMBDA(x,y,SUBSTITUTE(x,y,"№")))

上記では以下のバリエーションも置換対象に加えています：
大文字・小文字混合 (No. / no.)
全角バリエーション（Ｎｏ． / ＮＯ）

2. すべてのナンバー形式を包括するアプローチ
正規表現を活用すれば、ナンバー表示をより包括的に処理できます。ただし、これにはExcel内で正規表現を使えるようにする工夫が必要です。例えば：

Power Query を使用する方法 Power Query（Excel内蔵のデータ変換ツール）で正規表現を使えば、例えば以下のような形式を一括処理できます：

NO.123
No:123
Number 123
Power Query のステップ例：

置換ステップ: 正規表現でナンバーのパターン（例：No.*, NO.* など）を検出して № に変換します。
VBA を使用する方法 VBA で正規表現を使う方法を実装します。以下に簡単なコード例を示します：

Function ReplaceNumberFormat(inputText As String) As String
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "(?i)(no[\.:]?|number[\.:]?)"
    regex.Global = True
    ReplaceNumberFormat = regex.Replace(inputText, "№")
End Function

この関数をセルで使用できます：=ReplaceNumberFormat(受付!F2)
正規表現パターン (?i) は大文字・小文字を区別しない設定です。

3. 条件が複雑でない場合の手動更新のアプローチ
複雑な形式を網羅する必要がない場合、REDUCE 関数のリストに追加するだけで多くのケースに対応可能です。

まとめ
置換対象のパターンが増える場合は、数式の REDUCE にリストを拡張する。
正規表現が必要な場合は Power Query や VBA を利用する。
パターンが無限に広がる場合は、プログラミング的アプローチ（VBA）が最も柔軟です。
要件に応じた方法を選んでください！

Excelの数式について教えてください。

ご質問者の補足コメントに対し、No.1さんからの追加回答が付かないようです。

こんばんは

現状の数式では、特定のナンバー表示（{"NO.","NO","No.","No"}）にしか対応していないため、その他の形式には対応できません。

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング