Alhalford wrote :
Hello all!
I have a large email list in Excel and it comes with non printing
characters leading and trailing. I have used both TRIM and CLEAN
functions, neither work all the way. I am wondering if there is an
add-in for this or a program that would clean these? At this point I
am having to do this by hand... wasting LOTS of time!
Please help!!!
That's typical of imported lists that are delimited by carriage returns
and/or linefeeds. Here's a reusable function I use that will filter out
unwanted characters. It allows all alpha-numeric characters by default,
and you can specify other characters to be included. (In the case of
email addresses: "@-._" are the most common IncludeChars)
Function FilterString(ByVal TextIn As String, _
Optional IncludeChars As String, _
Optional IncludeLetters As Boolean = True, _
Optional IncludeNumbers As Boolean = True) As String
' Filters out all unwanted characters in a string.
' Args: TextIn The string being filtered.
' IncludeChars [Optional] Any characters to keep.
' IncludeLetters [Optional] Keeps any letters. Default=True
' IncludeNumbers [Optional] Keeps any numbers. Default=True
'
' Returns: String containing only the wanted characters.
Const sSource As String = "FilterString()"
'The basic characters to always keep
Const sLetters As String = "abcdefghijklmnopqrstuvwxyz"
Const sNumbers As String = "0123456789"
Dim i As Long, CharsToKeep As String
CharsToKeep = IncludeChars
If IncludeLetters Then _
CharsToKeep = CharsToKeep & sLetters & UCase(sLetters)
If IncludeNumbers Then _
CharsToKeep = CharsToKeep & sNumbers
For i = 1 To Len(TextIn)
If InStr(CharsToKeep, Mid$(TextIn, i, 1)) Then _
FilterString = FilterString & Mid$(TextIn, i, 1)
Next
End Function 'FilterString()
To use it in a cell formula:
(Assumes email addresses in col A, starting in A2)
In col B2, type ...
=filterstring(a1,"@-._")
...and copy down.
If you store the function in PERSONAL.XLS then to use it in other
workbooks you need to prepend the workbook name like this...
=personal.xls!filterstring(a1,"@-._")
--
Garry
Free usenet access at
http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
