Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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!!! :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Suppress Printing with Character in Excel File | Excel Discussion (Misc queries) | |||
Problem printing a large excel cell | Excel Discussion (Misc queries) | |||
excel list of names, addresses and email to address book/contact list??? | Excel Discussion (Misc queries) | |||
Excel 2007 Large Format Printing Issue | Excel Discussion (Misc queries) | |||
Printing from email without Word/Excel | Excel Worksheet Functions |