excel not finding spaces in email addresses when searching

[Seemore]

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

 

[Gord Dibben]

Could be the spaces are non-breaking spaces left over from an import from the
web or similar.

Edit>Replace will not find these.

You can try Edit>Replace

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