I have never written a cleanup routine for excel. All that I have done it write formulas and such.

I find myself in need of a routine that can be run over a set of cells (usually a column) that will strip out an extraneous character from the data set. The specific need at the moment is that I am copying a web page of financial data but the way that the financial institution puts the data up, I wind up with a tab character as part of each column. This isn't too bad except for the columns that contain numbers. I'd like a way to delete the trailing tab character or (much better) remove any non-number from the column/set. Of course, I'd like to keep the '-' sign for negative numbers.

Is there a macro already in MS land that can do this or can anyone give me an idea as to where to go to get the information or, if it is that simple, just post one?

Thanks in advance!!
[Reply]

Not sure how to do this with a canned macro, but I can write a VB macro do do this... Something like this clears the contents of column 2, rows 1 through 100:

Sub cleanup()

Dim i
For i = 1 To 100

' this clears the cell, but you can use simple or regular expressions to edit.
Cells(i, 2).Value = ""

Next

End Sub

[Reply]

Thanks, Michael. However, I am trying to take a cell with a number that looks like "123 " (note the space at the end) and removes all non-numbers to get "123" (no space). It's easy to do in C, since isanumber is there. There are specific instances where the number might not be terminated by a space, so I can't just delete the last character of the string. Also, I suspect that sometimes, that space is a tab.
[Reply]

TRIM should remove leading and trailing spaces.
Not sure if you anticipate "internal" spaces? Letters?
[Reply]

or use regexp - see the second answer here:
http://stackoverflow.com/questions/3...ction-or-regex

to remove anything but digits use \D instead of \d
[Reply]

Thanks, Julian. Looks like trim() will work, but I also played with the regex routine you pointed me to.
[Reply]