Large Excel Email list with Non printing character
 

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!!! :)

Large Excel Email list with Non printing character
 

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 ...

")

...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...

")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Large Excel Email list with Non printing character
 

I see my reader has added link format to my IncludeChars string. To
clarify, it consists of the AT symbol (Shift+2), a hyphen, a period,
and an underscore.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Large Excel Email list with Non printing character
 

Oops! Sorry about the typos...

In col B2, type ...

")

..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...

")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion