How to clean a cell value or a variable

[AJ]

Hi folks,

Got a weird-ish one here.
What I need to do is to find a quick way of removing all the following characters from a cell:
/

*
:
<
>
|
~
Basically all those characters that cannot be used in a filename as well as the tilde ~.
Alternatively, just remove any character that is not a alpha character or a number.
I also would like to dump extra white space (TRIM) and non-printable (CLEAN)

Here's the tricky bit though - I want to do it in one step, not with an endless procession of SUBSTITUTE, TRIM AND CLEAN.

In fact, to make it perfect, I don't really want to do it to a cell value at all, but rather a variable in VBA, although knowing the way to do it to a single cell would be just as good.

Any ideas?

Many thanks in advance
AJ

[s-o-s]

Here is some VB code that will clean up the values in the current cell. If you find you need to add more characters to the excluded list, just add them in to the instr line.

Sub Clean_It_up()
Dim CurVal As Variant
Dim Count As Integer
Dim CurChr As Variant
Dim NewVal As Variant

CurVal = ActiveCell.Value

For Count = 1 To Len(CurVal)
CurChr = Mid(CurVal, Count, 1)
If InStr(1, "/*:<>|~", CurChr) = 0 Then
NewVal = NewVal & CurChr
End If
Next

ActiveCell.Value = NewVal
End Sub

[AJ]

Brilliant - works a charm!
Thanks
AJ

[cazan]

I have found a need for this code too. But what modifications do I need to make to it in order to clean up a couple thousand items in a column with some cells being empty?

Thanks in Advance
Chris A. Z.

[Joe4]

cazan,

Here is a modification of the code posted that will work on a whole column. Change the range reference as needed:

Sub Clean_It_up()

    Application.ScreenUpdating = False

    Dim Count As Integer
    Dim CurChr As Variant
    Dim NewVal As Variant
    Dim myRange As Range
    Dim cell As Range
    
'   Enter range to clean (column A in this example)
    Set myRange = Range("A1:A" & Range("A65536").End(xlUp).Row)
    
    For Each cell In myRange
        NewVal = ""
        For Count = 1 To Len(cell)
            CurChr = Mid(cell, Count, 1)
            If InStr(1, "/*:<>|~", CurChr) = 0 Then
                NewVal = NewVal & CurChr
            End If
        Next
        cell.Value = NewVal
    Next

    Application.ScreenUpdating = True

End Sub

[cazan]

Thank you! I works great. I would image that if I were to clean a range or whole row, all I would have to do is place the row range where the column range, ("A1:A" & Range("A65536"), is located.

Is the row range syntax different than the column?

Thanks you,
Chris A. Z.

[DRJ]

Yeah, just change MyRange to whatever range you want and the macro will check every cell in that range.

[jholzman1]

There's also a super-easy utility out there -- check out http://www.asap-utilities.com. It's great. And one of the tools does exactly what you're asking for. (Plus, there's tons more.)

[Ekim]

In the previous macro, you may wish to change this line:

cell.Value = NewVal

to:

cell.Value = Application.Clean(Application.Trim(NewVal))

I note that the while the macro removes the designated special characters, the deleted characters are represented as spaces in the text string.

Notes:
VBA 's Trim function only trims leading and trailing spaces, not extra internal spaces. If you want to trim those as well, you need to use the worksheet Trim function (hence “Application.Trim”)

The CLEAN function removes all nonprintable characters from a string.

Regards,

Mike