Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel not finding spaces in email addresses when searching
I have a list of email address that I copy and paste into Outlook. I
keep getting errors in Outlook when sending my emails and the culprit appears to be extra spaces at the end of some of the email addresses. I have done file/replace and it found 66 and deleted the spaces. However, I am still having the issue and if I go down line by line and click in each cell, I will eventually find the one line which has extra spaces. Why can't excel find these and is there a better way of removing them? I have about 1500 addresses so going line by line really sucks. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel not finding spaces in email addresses when searching
Could be the spaces are non-breaking spaces left over from an import from the
web or similar. EditReplace will not find these. You can try EditReplace What: Alt + 0160 on numpad With: nothing Or go with David McRitchie's Trimall macro. Sub TrimALL() 'David McRitchie 2000-07-03 mod 2000-08-16 join.htm Application.ScreenUpdating = False Application.Calculation = xlCalculationManual Dim Cell As Range 'Also Treat CHR 0160, as a space (CHR 032) Selection.Replace what:=Chr(160), replacement:=Chr(32), _ lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False 'Trim in Excel removes extra internal spaces, VBA does not On Error Resume Next 'in case no text cells in selection For Each Cell In Intersect(Selection, _ Selection.SpecialCells(xlConstants, xlTextValues)) Cell.Value = Application.Trim(Cell.Value) Next Cell On Error GoTo 0 Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 10 Jun 2008 14:25:25 -0700 (PDT), Seemore wrote: I have a list of email address that I copy and paste into Outlook. I keep getting errors in Outlook when sending my emails and the culprit appears to be extra spaces at the end of some of the email addresses. I have done file/replace and it found 66 and deleted the spaces. However, I am still having the issue and if I go down line by line and click in each cell, I will eventually find the one line which has extra spaces. Why can't excel find these and is there a better way of removing them? I have about 1500 addresses so going line by line really sucks. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Email Addresses on Excel | Excel Discussion (Misc queries) | |||
Excel and Email addresses | Excel Discussion (Misc queries) | |||
Need help finding non-duplicate email addresses... | Excel Discussion (Misc queries) | |||
Email addresses in Excel need to format for mass email | Excel Worksheet Functions | |||
Finding email addresses in cells | Excel Worksheet Functions |