Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a lot of lines of text. I can put them into a text file or into an Excel spreadsheet. They need many changes. Here's an example of a before and after:

Input:  I'm buying one!  Only $39 .....
Output: I'm buying one - Only USD 39

In other words, the HTML code got converted to an apostrophe, the exclamation mark became a space-hyphen, duplicate spaces were stripped out, $ became USD, and ending dots and spaces were removed until none were left.

I am guessing two different processes may be needed -- first, a simple character substitution for all occurrences, and then a looping procedure that keeps reducing multiple instances (e.g., converting two spaces to one until there are no more two-space combinations).

Ideally, I would have a readily editable list, as in an Excel spreadsheet, with one cell for the "before" and an adjacent cell for the "after." Or at least I would ideally have that for the simple substitution part of the operation.

I've seen a bunch of macros and scripts, in various posts, but most of them aren't quite on target and some of them I don't understand. Any suggestions?

share|improve this question
I should have typed that example as code. I meant "Input" to begin one line and "Output" to begin the next. Also, I see my HTML code - ampersand pound 39 semicolon - got converted to an apostrophe. – Ray Woodcock Mar 25 '12 at 13:40
Have you tried opening it in an advanced text editor like notepad++ or programmer's notepad? – Raystafarian Mar 26 '12 at 11:15

I often use Microsoft Word for stuff like this. It's search and replace is very powerful and I find it easier then Excel for handling substitutions. I also use a Notepad replacement app called NoteTab ( There is a free "lite" version. Nice thing about Notetab is that it easily allows you to search across multiple files.

share|improve this answer
I think Word would have a couple of drawbacks for this purpose. One is that I'm not sure how the solution would be automated with a visible table of conversions. The other is that, in one application, I will be writing batch files to rename files. The command lines will need to contain both the "before" and the "after," whereas I think an automated solution in Word would tend to replace everything. It would change the "before," hence my batch commands would not work. – Ray Woodcock Mar 25 '12 at 16:36

This works if all your text is like the sample you provided. Just open your text file in Excel, and add a new sheet to create your table of replacement terms (with headers). I have it set up to read the first column as the "before" terms and the second column as the "after" terms. After doing this, press Alt+F11 to open the VBA editor pane. Insert a new module and paste in the following code.

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
            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
                tdots = 0
            End If
        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

Execute the code, then save your file as text.

share|improve this answer
This answer appears to have the best potential. I did not use it at the time, but will refer back to it the next time I return to this particular kind of task. Thanks! – Ray Woodcock May 24 '12 at 8:33

This is a nice addon (for Excel) that will do exactly what you want. It costs $30 USD (at the time of writing), so is great if you use it a lot from time-to-time or constantly, but if this is just a one-time use case then it may not be worth it.

It works by selecting remove/replace user selected characters, then you can select replace, tell it what characters you want to replace, then with what, and boom. It goes through all the selected cells and changes it. You can even add spaces in to make things clean.

share|improve this answer
But it looks like the user has to manually select one operation at a time. And several of those operations are just TRIM. For some operations, it might be a little easier than just writing a formula to do the same thing, but for $30 on an infrequent task, I'll probably pass. – Ray Woodcock Mar 27 '12 at 16:26

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .