Removing Spaces from a column list of emails

[jschwak]

I have searched the posts with no luck. I need a very simple program which will remove any spaces at the end of an email address so that I can copy and paste to outlook with no sending problems.


Only some of the thousands of email addresses I have stored in an excel column have two spaces at the end which I need to eliminate.

Thank you

 

[P Sitaram]

you can use CLEAN function to remove all incompatible characters, that are shown by excel as spaces.

 

[jschwak]

No sorry, the semicolon is automatcially added by outlook express. This is the reason I can have no spaces because it places the semicolon after these spaces creating an unrecognizable email address.

I followed your exact instructiosn which was good idea because it pased the values back in instead of the Trim function. Suprisingly enough though, spaces still existed after the email addresses.

Any other ideas??

Thanks so much!

 

[jschwak]

Clean did not get rid of the spaces after the email addresses

 

[Tom Urtis]

If you have an email address in cell A2, enter this formula in some other cell:
=CODE(RIGHT(A2,1))

What number does the formula produce?

 

[jschwak]

"117"

 

[Tom Urtis]

Well, that's the lower case letter u. Is the email address's domain .edu by any chance?

 

[jschwak]

thats correct.. all the email addresses are .edu and the value "117" is shown with the formula =CODE(RIGHT(A2,1)) whether there are spaces after the email address or not

 

[Tom Urtis]

Something is causing an inconsistency between your belief that there are "spaces" at the end of the email addresses, and what we just saw which is evidence to the contrary. The right-most character is a "u", not a spacebar character. I'm not doubting your word, just wondering why you think there are spaces when Excel doesn't think so, and we just demonstrated that there are not spaces? Am I missing something obvious? Why do you think there is a trailing space in the value? Maybe it's the way OE displays it after pasting.

 

[jschwak]

Any more ideas? Possibly macros?

 

[Ekim]

Jschwak,

As you have thousands of email addresses you may be interested in the following simple macro.

The macro will Clean and Trim your data (see the Notes at the end).

Put the macro into a standard module:
- Right click a worksheet tab
- Select View - this will take you to the VBA editor (VBE)
- In the VBE, go to the Insert menu and click “Module”
- In the white space that opens up, copy/paste the macro
- Alt-Q (hold down the Alt key and press Q) – this will close the VBE and take you back to your worksheet.

To run the macro, either:
1. Go to the Tools menu, select Macro, then Macros. Highlight the macro’s name (“trimIt”), then hit the Run key.
2. Assign the macro to a button from the Forms menu (post back if you require help with this).

The macro assumes that your email addresses are all in column A.

Sub trimIt()
Dim x As Long
    x = Range("A63556").End(xlUp).Row
    For i = 1 To x
        Cells(i, 1) = Application.Clean(Application.Trim(Cells(i, 1)))
    Next
End Sub

Notes:
The CLEAN function removes all nonprintable characters from text.

The TRIM function removes all spaces (including leading and trailing spaces) from text except for single spaces between words.

HTH

Mike