VBA Code -- Cleaning Data
You can adapt this function by Leo Heuser to remove any characters
that you want. Just change what's inside the array.
Function ReplaceIllegalChars(Filename As String) As String
'' Replaces illegal filename characters with a space.
, 5. August 2001
Dim Illegal As Variant
Dim Counter As Integer
Illegal = Array("<", "", "?", "[", "]", ":", "|", "*", "/")
For Counter = LBound(Illegal) To UBound(Illegal)
Do While InStr(Filename, Illegal(Counter))
Mid(Filename, InStr(Filename, Illegal(Counter)), 1) = " "
Loop
Next Counter
ReplaceIllegalChars = Filename
End Function
HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------
I have some code that is supposed to clean data in a
particular cell. The data is part numbers. Example...
A1 B1
P/N 1234-5 P/N 12345
When you enter the function cleanse(A1) in B1, the cleaned
part number will appear. It took out the dash. There are
other characters I want to do this with.
|