2013-10-16 13:34:39 +0000 2013-10-16 13:34:39 +0000
13
13

セル内の特殊文字を識別するためのエクセルの式はありますか？

約3500の文書がありますが、そのファイル名を手動でスクラブして、括弧、コロン、セミコロン、カンマなどの特殊文字を削除する必要があります。

エクセルにダンプしたテキストファイルがあるのですが、ファイル名に特殊文字が含まれている場合に修正のフラグを立てる列を作成しようとしています。擬似コード式は、

=IF (cellname contains [^a-zA-z_-0-9], then "1", else "0")

で、A-Z, 0-9, - or _以外の文字が含まれている場合は、大文字小文字に関係なく、行にフラグを立てるようにします。

誰か私のために働くかもしれない何かを知っていますか？手っ取り早くて簡単なものがあれば、大規模なif文をコード化するのを躊躇しています。
ソース

回答 (4)

19
19
19
2013-10-16 14:26:04 +0000

コードがない？でも短くて簡単で綺麗だし… :(

あなたのRegExパターン[^A-Za-z0-9_-]は、すべてのセル内のすべての特殊文字を削除するために使用されています。

Sub RegExReplace()

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True

    RegEx.Pattern = "[^A-Za-z0-9_-]"
    For Each objCell In ActiveSheet.UsedRange.Cells
        objCell.Value = RegEx.Replace(objCell.Value, "")
    Next

End Sub

編集

これがあなたの元の質問に限りなく近いです。

Function RegExCheck(objCell As Range, strPattern As String)

    Dim RegEx As Object
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = strPattern

    If RegEx.Replace(objCell.Value, "") = objCell.Value Then
        RegExCheck = 0
    Else
        RegExCheck = 1
    End If

End Function

2つ目のコードは、2つの引数を持つユーザー定義関数=RegExCheck(A1,"[^A-Za-z0-9_-]")です。最初の引数はチェックするセルです。2 番目はチェックする RegEx パターンです。

最初にVBAエディタをALT+F11で開き、新しいモジュール(!)を挿入し、以下のコードを貼り付ければ、他の通常のExcelの数式と同じように使用できます。

[] stands for a group of expressions
^ is a logical NOT
[^] Combine them to get a group of signs which should not be included
A-Z matches every character from A to Z (upper case)
a-z matches every character from a to z (lower case)
0-9 matches every digit
_ matches a _
- matches a - (This sign breaks your pattern if it's at the wrong position)

RegEx を初めて利用する方のためにパターンを説明します: [^A-Za-z0-9_-]

ソース
7
7
7
2013-10-16 15:31:19 +0000

nixdaのコードと似たようなものを使って、セルに特殊文字がある場合に1を返すユーザ定義関数を以下に示します。

Public Function IsSpecial(s As String) As Long
    Dim L As Long, LL As Long
    Dim sCh As String
    IsSpecial = 0
    For L = 1 To Len(s)
        sCh = Mid(s, L, 1)
        If sCh Like "[0-9a-zA-Z]" Or sCh = "_" Then
        Else
            IsSpecial = 1
            Exit Function
        End If
    Next L
End Function

ユーザー定義関数(UDF)のインストールと使用は非常に簡単です。

  1. ALT-F11 で VBE ウィンドウを表示
  2. 2. ALT-I ALT-M は新しいモジュールを開きます
  3. ペーストして VBE ウィンドウを閉じます

ワークブックを保存すると UDF も一緒に保存されます。

UDFを削除するには:

1.上記のようにVBEウィンドウを開く 2.コードをクリアする 3.VBEウィンドウを閉じる

UDFをExcelから使用するには。

=IsSpecial(A1)

マクロ全般についての詳細は以下を参照してください。 http://www.mvps.org/dmcritchie/excel/getstarted.htm

と http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

と http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

UDF の詳細については以下を参照してください

動作させるにはマクロを有効にする必要があります!
ソース
2
2
2
2013-10-16 21:05:57 +0000

ここでは、特殊文字を含むレコードにフラグを付ける条件付き書式設定のソリューションを紹介します。

以下の（非常に長い）式を使用する新しい条件付き書式設定ルールをデータに適用してください。残念ながら、許容される文字コードはすべて連続しているわけではないので、この式では A1 の合計を使用しなければなりません。この数式は、存在する不良文字の数を返します。0より大きい値を返すセルはすべてフラグが立てられます。

例: =SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<48)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>45))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>57)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<65))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>90)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<97)*(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))<>95))+SUMPRODUCT((CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))>122)*1)

ソース
1
1
1
2016-06-20 21:36:00 +0000

特殊文字を見つけるために、別の方法を使用しました。許可された文字ごとに新しい列を作成し、次のような式を使用して、その許可された文字が各行エントリ(Z2)に何回含まれているかをカウントしました。

AA2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AA$1,""))
AB2=LEN($Z2)-LEN(SUBSTITUTE($Z2,AB$1,""))
...

そして最後に、最後の列(BF2)でソートしてマイナスの値を見つけ、修正が必要な列にたどり着きました。
ソース