Using Excel to Clean Unwanted Characters from Filenames or Other Text

I was trying to organize some files.  This post contains some notes I made on ways of using Microsoft Excel to change a list of filenames.  The renaming project drew on file renaming techniques described elsewhere.

In various attempts in this process, I came up against situations where inconsistent punctuation and other filename characteristics made the process unnecessarily complicated.  For instance, file names might refer to "Barack Obama," "Barack H. Obama," "President Obama," and so forth.  These would all be alphabetized differently.  Duplicates might be concealed, and files that belonged together topically might be overlooked.

In part, that was an issue of choosing and using consistent file naming conventions.  But at this point, given that the files were already named, there was an issue of how to deal with punctuation and other non-alphanumeric characters.  I wondered if there was a relatively straightforward way to get my arms around the varieties of character usage within a particular corpus.

The first task seemed to be identifying what unwanted characters were in use. Taking Microsoft Excel as my weapon du jour, I made a list of the filenames that I might want to change, using DIR on the Windows command line (see the comments following another recent post). I pasted that list of filenames into Excel.

Next, I ran a search and found several ingenious approaches to related problems. One would use a wildcard search-and-replace in Microsoft Word:   search for ? (making sure to indicate that this would be a "Use wildcards" search) and replace with ^&^p to put the same character (that is, any character) on a line by itself; then paste the whole text into Excel and do a unique filter to get all of the unique characters found in the text. This did not seem likely to work for my purposes, unfortunately. For one thing, Excel 2003 was limited to 65,536 rows, and there could easily be more characters than that in a text being analyzed.  I had also found that spreadsheets capable of handling more rows could be slow at it.

There were other approaches that used an array formula or a script. I was not as comfortable with those that I did not understand, because it would be difficult for me to work through their logic and make sure they were really doing what I wanted them to do. An unnoticed mistake could have unfortunate effects when altering a large or important text, or when renaming thousands of files.

Another possibility came to mind as I looked at the suggestion to use MoreFunc's REGEX.COUNT function (alternately, Excel's SUBSTITUTE or possibly MATCH) to produce character-by-character counts.  I ran a search and found some interesting variations in SUBSTITUTE, including nested and case-insensitive substitutions, along with the CLEAN function. One solution, it seemed, would be to use 26 columns containing something like =SUBSTITUTE(UPPER(A1),"","") to replace all upper- and lower-case letters with nothing, another 10 columns to do the same for numbers, and then see what's left.

With SUBSTITUTE, one possibility was to set up a map. Put the rows of text being analyzed in one worksheet ("Sheet1"). Create a second worksheet ("Sheet2") within the same Excel file.  In Sheet2, make each row relate to the row of the same number in Sheet1. So, for example, if there was a text string in cell A5 in Sheet1, there would be a row of characters across row 5 in Sheet2. Each cell in that row would count the occurrences of a specific character. For example, the 65th column would detect occurrences of the character whose Excel code happened to be 65 (that is, the letter "A," producible by =CHAR(65)).  The chief problem with this approach was that its calculations would take a year.  It would also max out Excel 2003's limit of 256 columns.  It would have the advantage of transparency:  I would be able to see much of what was happening in the data.

I found a user-defined function (UDF) -- what I had been referring to as a script -- called SUBSTITUTELIST.  This UDF would let me indicate a list of characters to simply remove.  In naming files, I would definitely not want to include many of the odd characters that were readily visible if, say, I opened a non-text file in Notepad.



The first step, with SUBSTITUTELIST, was to define it as a function that I could use in spreadsheet formulas.  To do that, using the steps described atop another post, I copied and pasted this text into an Excel module:

Function SubstituteList(rString As String, rList As Range) As String
Dim rCell As Range
For Each rCell In rList
rString = Replace(rString, rCell.Text, "")
Next rCell
SubstituteList = rString
End Function

I didn't write that code, and was only partly clear on what it said.  It appeared, though, that its middle line was going to replace rString with nothing (""). In other words, rString was being removed. Now I had to tell it what characters to treat as rString. In the spreadsheet listing the filenames I wanted to work on, I opened a new worksheet, called Sheet2, and put the numbers 1 through 255 in cells A2 to A256. (Two easy ways to insert a series of numbers: use =A1+1 in cell A2, and so forth; or enter the first two numbers (i.e., 1 and 2) manually, highlight the cells to be filled, and then use Edit > Insert > Series. The Alt command option would probably still work in post-2003 versions of Excel: Alt-E, I, S, Enter.)

Then, in Sheet2 cell B1, I typed =CHAR(A1), and copied it down to B255. So now column B contained the actual characters I wanted to look at, whose CHAR equivalents were shown in column A. Finally, column C contained the characters to be removed from consideration.  For example, in cell C128, I typed =B128.  That put a copy of CHAR(127) into column C.  SUBSTITUTELIST was going to remove anything in column C.  So that particular character would be removed from the cell in Sheet1 where I was going to be using SUBSTITUTELIST.  Depending on the situation, I might or might not want to remove all non-alphanumeric characters.  For instance, I thought that what I would really want to do with something like the euro character (€), in a filename, would not be to eliminate it, but rather to replace it (e.g., €35 becomes EUR 35).

To use SUBSTITUTELIST, I put my text to be analyzed in column A of Sheet1. In B1, I entered this: =SUBSTITUTELIST(A1,Sheet2!$C$1:$C$255). I copied that formula all the way down, next to each text string to be examined. That gave me a collection of funky characters in Sheet1 column B. Originally, I added TRIM to the SUBSTITUTELIST formula just cited, but then I realized that I actually wanted to see the pattern of spaces and other non-alphanumeric characters.

SUBSTITUTELIST seemed to have potential for single-character eliminations, but it didn't seem capable of substitutions involving multiple-character strings (e.g., EUR in place of €).  It looked like a relatively simple change to SUBSTITUTELIST might do the job, but I had little ability for that sort of work at this point, so I posted a question on it.  One reply to that question suggested a UDF called CLEANUPTEXT.  The contents of that UDF (created in the same manner as SUBSTITUTELIST, above) were as follows:

Option Explicit
Sub cleanupText()

Dim allTxt() As Variant, sublist() As Variant
Dim i As Long, j As Long, k As Long, tdots As Integer

'Store data from sheets in arrays.
allTxt = Sheets(1).UsedRange.Value
sublist = Sheets(2).UsedRange.Offset(1, 0).Resize(Sheets(2).UsedRange.Rows.Count - 1, Sheets(2).UsedRange.Columns.Count).Value

For i = 1 To UBound(allTxt, 1)
    For j = 1 To UBound(allTxt, 2)
        'Loop through replacement terms and make replacements to data in array.
        For k = 1 To UBound(sublist, 1)
            allTxt(i, j) = Replace(allTxt(i, j), sublist(k, 1), sublist(k, 2))
        Next k
        allTxt(i, j) = Trim(allTxt(i, j))
        'Remove series of trailing periods.
        If Right(allTxt(i, j), 1) = "." Then
            tdots = 1
        Else
            tdots = 0
        End If
        Do While tdots = 1
            allTxt(i, j) = Left(allTxt(i, j), Len(allTxt(i, j)) - 1)
            If Right(allTxt(i, j), 1) = "." Then
                tdots = 1
            Else
                tdots = 0
            End If
        Loop
        allTxt(i, j) = Trim(allTxt(i, j))
    Next j
Next i
'Print cleaned up results in array onto sheet.
ActiveSheet.UsedRange.Value = allTxt
End Sub

The instructions accompanying that UDF seemed to indicate that it required a separate worksheet, called Sheet2, containing the "before" terms in column A of Sheet2, and the "after" terms in column B of that sheet; and Sheet1 had to be the name of the tab containg the text being operated on.  In response to my question, the text shown in blue (above) was designed to remove trailing dots from the text being modified.  I did not actually try this UDF, but I hoped that I would not have to write similar lines of text for other kinds of unwanted text strings -- that, in other words, the column A / column B replacement approach would suffice.  If so, I might try to simplify this UDF by removing the lines in blue.

On a separate note, I had noticed that some of the files in my list looked like their names were identical except for punctuation, so I added a column, in my spreadsheet that listed filenames, for this formula:  =LOWER(substitutelist(M2,Sheet2!$C$1:$C$255)).  M2 contained the existing filename, or part thereof, and Sheet2 column C was set to eliminate all non-alphanumeric characters, as shown above.  This step did succeed in highlighting some duplicate filenames and other issues.

Another problem was that, in some cases, I would have to rename files before proceeding with something like SUBSTITUTELIST.  The specific problem was that some characters in filenames would defeat batch commands.  That is, I could have a command to change a filename, perhaps in response to an insight emerging from SUBSTITUTELIST; but the command might not run.  So I would have to change or delete those troublesome characters first.  (I often used Excel formulas to generate commands that I could run in a batch file to rename numerous files.  The basic idea here was that I would put the existing filename in cell A1 of an empty worksheet, and the desired filename in cell B1, and then I would enter this formula in cell C1:  ="ren "&CHAR(34)&A2&CHAR(34)&" "&B2. (If the new filename in B2 had spaces in it, then it too would have to be surrounded by CHAR(34) instances, as done in that formula for cell A1.)  The particular chacters at issue here were percent symbols and possibly exclamation marks.  The commands were right.  They would run if I typed them into the command line, or copied and pasted them there.  But they wouldn't work from within a batch file.  To address this concern, I just deleted the exclamation marks, and I replaced the percent symbols with "pct" (e.g., 45% became 45pct).

In some cases, I would want a tool that could replace one or more undesirable characters, in a single bulk process, with one or more preferred characters.  In part, this was a simple interest in transliteration or Romanization (i.e., converting foreign-language characters to the standard English alphabet). There would be at least two reasons to do that. One was that I had run into some file handling issues with some such characters. I recalled one recent case where something that looked like a C (maybe ḉ or ć or ĉ or ċ or č) was not working correctly with some backup program or something. The other reason was that I might not remember which character to use when doing filename searches.  So, for instance, if I was looking for all of the files containing a copy of someone's resume, I might or might not want to have to search for both "resume" and "résumé."  There were many Romanization tables for various languages, possibly best approached by finding conversion tables for the thousands of possible Unicode characters.  This could take further attention, as there were multiple kinds of Unicode, not all of which might be consistent with Excel's coding.  In filenames, especially, examples involving several characters could include "pct" (above) and the replacement of $ with USD and of © with (C).  One readability example, arising where HTML has become involved, would be to replace the HTML code of ' with the apostrophe.

As I thought further about the problem, I realized that characters might be unwanted in context, not in isolation. For instance, the hyphen ("-") could be fine in some settings, not fine in others. At the risk of opening the door to complexity, it seemed the ideal approach (perhaps the less messy approach, in the long run) would give me the possibility of listing multi-character combinations and indicating their desired replacement. I found a UDF called SUPERSUB that seemed to be just what the doctor ordered:

Function SuperSub(OriginalText As String, rngOldText As Range)
    Dim cel As Range
    Dim strOldText As String, strNewText As String
     'loop through list of old_text used in substitute
    For Each cel In rngOldText.Cells
        strOldText = cel.Value
        strNewText = cel.Offset(0, 1).Value
        OriginalText = Application.WorksheetFunction.Substitute(OriginalText, strOldText, strNewText)
    Next cel
    SuperSub = OriginalText
End Function

But, again, I did not try this script at the time, but was merely preserving its information here for possible future application.

Another post offered a UDF called REMOVEPUNCTUATION where I could create a list of items to replace, and their replacements, within the macro.  The text of that one (with a few illustrative examples) was as follows:

Sub test()
Dim c As Range, t As String
For Each c In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    t = UCase(" " & RemovePunctuation(c.Text) & " ")
    t = Replace(t, " AVE ", " AVENUE ")
    t = Replace(t, " BLVD ", " BOULEVARD ")
    t = Replace(t, " BVD ", " BOULEVARD ")
    t = Replace(t, " CYN ", " CANYON ")
    t = Replace(t, " CTR ", " CENTER ")
    t = Replace(t, " CIR ", " CIRCLE ")
    t = Replace(t, " CT ", " COURT ")
    t = Replace(t, " DR ", " DRIVE ")
    t = Replace(t, " FWY ", " FREEWAY ")
    t = Replace(t, " HBR ", " HARBOR ")
    t = Replace(t, " HTS ", " HEIGHTS ")
    t = Replace(t, " HWY ", " HIGHWAY ")
    t = Replace(t, " JCT ", " JUNCTION ")
    t = Replace(t, " LN ", " LANE ")
    t = Replace(t, " MTN ", " MOUNTAIN ")
    t = Replace(t, " PKWY ", " PARKWAY ")
    t = Replace(t, " PL ", " PLACE ")
    t = Replace(t, " PLZ ", " PLAZA ")
    t = Replace(t, " RDG ", " RIDGE ")
    t = Replace(t, " RD ", " ROAD ")
    t = Replace(t, " RTE ", " ROUTE ")
    t = Replace(t, " ST ", " STREET ")
    t = Replace(t, " TRWY ", " THROUGHWAY ")
    t = Replace(t, " TL ", " TRAIL ")
    t = Replace(t, " TPKE ", " TURNPIKE ")
    t = Replace(t, " VLY ", " VALLEY ")
    t = Replace(t, " VLG ", " VILLAGE ")
    t = Replace(t, " APT ", " APARTMENT ")
    t = Replace(t, " APTS ", " APARTMENTS ")
    t = Replace(t, " BLDG ", " BUILDING ")
    t = Replace(t, " FLR ", " FLOOR ")
    t = Replace(t, " OFC ", " OFFICE ")
    t = Replace(t, " OF ", " OFFICE ")
    t = Replace(t, " APT ", " APARTMENT ")
    t = Replace(t, " STE ", " SUITE ")
    t = Replace(t, " N ", " NORTH ")
    t = Replace(t, " E ", " EAST ")
    t = Replace(t, " S ", " SOUTH ")
    t = Replace(t, " W ", " WEST ")
    t = Replace(t, " NE ", " NORTHEAST ")
    t = Replace(t, " SE ", " SOUTHEAST ")
    t = Replace(t, " SW ", " SOUTHWEST ")
    t = Replace(t, " NW ", " NORTHWEST ")
    c = Trim(t)
Next
End Sub

Function RemovePunctuation(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[^A-Z0-9 ]"
    .IgnoreCase = True
    .Global = True
    RemovePunctuation = .Replace(r, "")
End With
End Function

It seemed that, if I used REMOVEPUNCTUATION to make a large number of changes (more than just the few dozen shown here), I might use Excel to generate the text of the additional lines to be inserted into the REMOVEPUNCTUATION script.

That was the extent of these notes.  I would have to await another situation requiring mass file renaming in order to test these techniques.

Windows 7: A Shared, Relocated Start Menu

As described in another post, in Windows XP I had moved my Start Menu to a separate location.  That location was on a networked drive, where it would be backed up regularly, would not be affected by Windows reinstallations (as long as I reinstalled programs on each computer back to the same places as before), and would be available to multiple machines as needed.  It had grown into a monster that included not only shortcuts to installed programs but also the full contents of portable programs.

Now that I was installing Windows 7, I wanted it to function the same way.  Unfortunately, Windows 7 was not set up to accommodate this desire.  Apparently there had been a NoSimpleStartMenu registry option in Vista that would have returned the system to the classic WinXP Start Menu, but I wasn't finding much guidance on that registry tweak in Win7.  In my own experimentation, it didn't work.

There were some third-party utilities that would give me back the classic Start Menu.  I installed and played briefly with one of them.  It was the Classic Start Menu option within Classic Shell.  (Apparently there was at least one other utility calling itself Classic Start Menu, but that one wasn't free.)  Classic Windows Start Menu was another option:  it would apparently give me a Windows 7 Aero style combined with classic functionality, which sounded great.  It seemed to be a bit clunky, though, in the sense that it added entries for Create Folder, Create Shortcut, and Sort in each submenu.  It didn't look like it offered as many settings as Classic Start Menu, but that initial impression could have been mistaken.

Classic Start Menu seemed to be the most developed utility for changing at least the looks if not also the functionality of the Win7 Start Menu.  In my brief introductory dabbling, it was actually better than the original WinXP Start Menu in terms of configurability, and it left the original Win7 Start Menu still available via Shift-Click.  Because of its features, I decided to stay with it for a while, if possible, rather than just use a relatively simple set of registry edits and adjustments that might have accomplished some of the same results.  The idea behind those registry edits was to rewire the Favorites option on the Win7 start menu so that it would point to C:\ProgramData.

That pointer to C:\ProgramData might have been useful, for my purposes, if C:\ProgramData had been customizable.  It just wasn't.  It appeared that, as I installed more programs, it (and therefore a Start Menu built around it) would get very cluttered.  I had the same concern about Classic Start Menu:  I couldn't tell how to redirect it to look at the network drive.  Their FAQs didn't have a question on exactly that.  It sounded like their main plan was that I should drag and drop items into their settings dialog.  This, I thought, would not work for a shared Start Menu:  the changes would take effect only on the computer where I made them.  There appeared to be some possibility of future expansion in their Settings > Customize Start Menu tab > Programs Menu > right-click > Edit Item > Link option, but presently that option was grayed out.  The Classic Shell people had a list of alternative utilities, but it sounded like they tended to have bugs.  I looked at their forum, but didn't see a question on relocating the Start Menu, so I posted a question on it.  The question got no response.

I tried to take a look at C:\Documents and Settings, which is where the Start Menu shortcuts were saved in WinXP.  (This was a hidden folder; I had already adjusted settings in Windows Explorer > Tools to see it.)  When I went to that folder in Windows Explorer, I got an error:  "Location is not available.  C:\Documents and Settings is not accessible.  Access is denied."  A search on that led to a page that seemed to be telling me that, actually, Documents and Settings was just a link to C:\Users.  Another page said C:\Documents and Settings was a "junction point" that existed for backwards compatibility.

So I tried C:\Users instead.  That opened up.  There wasn't a Start Menu under Ray (my original username) because I had deleted that username.  I was only using Administrator.  I saw a Start Menu under Administrator, and I also saw one under All Users, as well as one under Default.  There might also have been one under Default User too; but when I tried to go into there, or into any of these several other Users subfolders, I got the "not accessible" error again, even though I was logged in as Administrator.  To defeat that, I entered a command to turn off User Access Control (UAC):

C:\Windows\System32\cmd.exe /k %windir%\System32\reg.exe ADD HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System /v EnableLUA /t REG_DWORD /d 0 /f

But that didn't do it.  The message was still basically the same:  "C:\Users\All Users\Start Menu is not accessible."  (Later, I saw that Win7 had a message for me, saying that I had to reboot to turn off UAC.)  I did another search and found a page that said that, actually, that was a junction point too.  Where I *really* wanted to go under each username there at C:\Users, it seemed, was the AppData\Roaming\Microsoft\Windows\Start Menu folder.  I found those folders for Administrator and All Users.  So -- wasn't there a registry location pointing the Start Menu to those folders, the way WinXP's registry would point to specific folders (and could be modified to point to others instead)?

As I thought about it, I wondered if I could use the approach (above) of doing a registry edit that would rewire the Favorites button in the classic Start Menu, and have it point to my shared Start Menu at D:\Installation\Start Menu.  That had already occurred to me, but the problem there was that, if I couldn't move items from C:\ProgramData, my Start Menu on D would be just ignoring the items on C.  But now I realized that I probably could create shortcuts to the items on C, and put those in my Start Menu on D.

So I started to do the registry tweak as described in the sources cited above.  But when I got to HKCU\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders, I saw that there was in fact not only an entry for Favorites, which is what the abovementioned tweak would use, but also for Start Menu itself.  Well.  So why not just redirect that?

My WinXP registry edits had redirected a number of items in that same folder, having to do with not only the Start Menu but also with other folders.  I decided to see if I could retain most of those redirections in the .reg file I was preparing to tweak new Windows 7 installations.  But for present purposes, I basically closed other programs, ran a .reg file with lines like these:

[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\Shell Folders]

"Administrative Tools"="D:\\Installation\\Start Menu"
"Programs"="D:\\Installation\\Start Menu\\Programs"
"Startup"="D:\\Installation\\Start Menu\\Programs\\Startup"
"Start Menu"="D:\\Installation\\Start Menu"
[HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Explorer\User Shell Folders]
"Programs"="D:\\Installation\\Start Menu\\Programs"
"Startup"="D:\\Installation\\Start Menu\\Programs\\Startup"
"Start Menu"="D:\\Installation\\Start Menu"

and sat back to see what would happen.  The registry changes were visible instantly in regedit.  On reboot, Win7 gave me an error message:

Location is not available
D:\Current refers to a location that is unavailable.

That was one of the locations I had specified in the reg file.  None of the registry edits had worked; I was still looking at the same Start Menu, or less.  Worse, the system wouldn't even run Windows Explorer.  I restarted in Safe Mode (i.e., I held F8 after the first BIOS bootup screen), but decided to try the System Recovery option instead.  But the last System Restore was even older than the most recent image backup, which seemed odd, so I went with the image.  (Apparently I was tired.  This was at day's end.  I didn't try checking the "Show more restore points" when I ran rstrui.exe.)  I probably could have just restored my backup of the affected registry keys, but I decided this would be a good first chance to test Win7's built-in system image feature.  I ran it and went to bed.

When I got up, the image restore had worked, so I tried some of those registry edits again, one at a time.  This time, though, I saved System Restore points along the way, using Control Panel > System > System protection > Create.  I saw that I had left out some steps.  First, as described in a separate post, I tried to move the fonts folder.  That failed.  It was OK; it was just an attempt to avoid having to save any new fonts in a separate location, to be restored as an extra step of any future system reinstallation. 

Some time passed, as I worked on other parts of the Win7 installation.  Installation of other programs added more icons to the Start Menu.  When those other installations were done, I came back to this draft post.

I decided to keep using the Classic Start Menu utility.  It worked for my purposes.  I saved its settings to a backup file.  With the registry edits shown in my post entitled "Windows 7 Installation - First Try," I was able to get the Start button to point to my shared Start Menu at D:\Installation\Start Menu.  With Classic Start Menu running, I clicked on Start > right-clicked on Programs > Properties > Location .  I specified the place on drive D where I kept my customized Start Menu > Programs folder.  I clicked Move and then closed out of that dialog.  I then went into C:\ProgramData\Microsoft\Windows\StartMenu and into the AppData\Roaming\Microsoft\Windows\Start Menu folder under each username at C:\Users, to see if there were any other program shortcuts that I would want to move to that location on D, or any other installed programs there to which I would want to link by shortcut.  There were none.

Other than some rearranging, that was it.  I had a Windows 7 Start Menu on drive D.  Drive D could be a network share, or it could be mirrored to other computers, as described in another post I was developing at about this same time.