Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

We have about 3500 documents whose filenames need to be manually scrubbed to remove special characters like brackets, colons, semicolons, commas, etc.

I have a text file that I've dumped into excel, and I'm trying to create a column that flags the filename for modification if it includes special characters. The pseudocode formula would be

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

to flag the row if it contains any characters other than A-Z, 0-9, - or _, regardless of case.

Anyone know of something that may work for me? I'm hesitant to code and massive if statement if there's something quick and easy.

share|improve this question
    
Is there a particular reason you're doing this text processing task in Excel? Even limiting oneself to Microsoft Office tools, it's a fairly simple search & replace in Word. Table with two columns, original filename and processed file name. –  mpez0 Oct 16 '13 at 15:27
    
These are index entries from a 3rd party software. This software outputs filenames that include colons, brackets, ampersands, etc., causing exceptions in conversion programs. We need to scrub the data in the 3rd party software before converting; the vendor does not provide an API for automating that task. I have a list of filenames in a text file. I am using Excel to create a flag based on the presence of special characters in a file name. Powershell, c# and Java return inaccurate results because the special characters are interpreted as operators. –  dwwilson66 Oct 16 '13 at 15:45
1  
I do want to point out that you're probably using the wrong tool for this. I can think of a couple ways of doing this pretty quickly in Notepad++, for example. You could even import the results into Excel at the end and have a column of 1s and 0s. –  Dane Oct 16 '13 at 16:51
    
@Dane Good to know about NP++. I'll have to explore that. I have the tool installed, but not a lot of experience with it. Thanks for the tip. –  dwwilson66 Oct 16 '13 at 17:03
add comment

3 Answers 3

up vote 10 down vote accepted

No code? But it's so short and easy and beautiful and... :(

Your RegEx pattern [^A-Za-z0-9_-] is used to remove all special characters in all cells.

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

Edit

This is as close as I can get to your original question.

enter image description here

The second code is a user-defined function =RegExCheck(A1,"[^A-Za-z0-9_-]") with 2 arguments. The first one is the cell to check. The second one is the RegEx pattern to check for. If the pattern matches any of the characters in your cell, it will return 1 otherwise 0.

You can use it like any other normal Excel formula if you first open VBA editor with ALT+F11, insert a new module (!) and paste the code below.

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

For users new to RegEx I'll explain your pattern: [^A-Za-z0-9_-]

[] 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)
share|improve this answer
    
The problem is that I don't want to REPLACE the characters, just flag them on the list so I can hand it off to someone else. The filenames are created by third-party software & need to be manually changed within that program...I just need to flag yes or no. That being said, I think I can modify your Replace code to flag the column instead. :) –  dwwilson66 Oct 16 '13 at 15:08
    
to implement this...it's just a matter of cutting and pasting into a new module & saving, right? or do I need to do something else? the formula =RegExReplace(cell) is not recognized....and I'm a bit rusty on creating new functions. –  dwwilson66 Oct 16 '13 at 15:15
    
@dwwilson66 Updated! –  nixda Oct 16 '13 at 16:43
add comment

Using something similar to nixda's code, here is a user defined function that will return 1 if the cell has special characters.

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

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=IsSpecial(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

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

and

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

for specifics on UDFs

Macros must be enabled for this to work!

share|improve this answer
add comment

Here's a conditional formatting solution that will flag the records with special characters.

Just apply a new conditional formatting rule to your data that uses the (extremely long) formula below, where A1 is the first record in the column of file names:

=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)

This formula checks each character of each filename and determines if its ASCII code is outside the allowable character values. Unfortunately, the allowable character codes are not all contiguous, so that's why the formula has to use sums of SUMPRODUCTs. The formula returns the number of bad characters there are. Any cells that return a value greater than 0 are flagged.

Example: enter image description here

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.