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

 

[jschwak]

First of all. Abou 5% of the email addreses have spaces. For one, the function bar (fx) shows there to be two spaces after certain email addresses, but not all of them. Second of all, when the email addresses are copied into outlook express, the majority of them are formatted correctly ("name@place.edu;") but like I said about 5% are formatted incorrectly because of the apparent space after the address ("name@place.edu ;" Since outlook express is discriminating agains the addresses that have the apparent space in the functin bar, it seems fairly clear that a minority of the addresses have spaces.

I didnt think this would be so difficult :wink:

Thanks for the help

 

[Tom Urtis]

Try the code formula on one of the cells you know belongs to the 5%. maybe cell A2 is among the 95%.

 

[jschwak]

Alright, Im getting closer. Thank you to everybody who is helping me out. I ran the macro... it seemed to work on cell A2 and it cleared up the spaces, but it did not work on some of the other cells. I dont know if this has antyhing to do with the fact that excel is reading the text as just that... text and not email addreses. The only part I did not complete of the macro instructions was the "2. Assign the macro to a button from the Forms menu (post back if you require help with this). " I dont know if this will make a difference to the result.

Also, I ran the =CODE(RIGHT(A2,1)) on all the B cells and some of the cells came up with the value 160 while some came up with the value 117

Thanks again for the help

 

[Tom Urtis]

Select all of column A (assuming that's where your email addresses are listed).
Click on Edit > Replace
Click in the Find What box, then hold down the Alt key and while it's held down, with your other hand enter the numbers 160
Let go of all keys.
Make sure there is nothing entered in the Replace With box.
Click Replace All.

No macro needed. Any joy?

 

[jschwak]

haha,

holding down alt key + 160 gave me this symboal "á" and it could not be found on the worksheet and excel "could not find any data to replace"

good thinking though... I attempted a replace all earlier but could not get it to work either

 

[Tom Urtis]

Try it again, this time Alt 0160.

 

[Tom Urtis]

If the above doesn't work, try this macro, and if this doesn't work I'm out of ideas.

Again, assumes column A.


Sub Macro3()
On Error Resume Next
Columns(1).Replace What:=Chr(160), Replacement:="", LookAt:=xlPart
End Sub

 

[jschwak]

THERE IT IS!!!! Worked like a Charm!!!

Out of curiousity... where do you find those character values in case I ever need them in the future which I'm sure I will.

Tom, thank you for helping me work through this and everyone else who lent some excel expertise. i really appreciate it!

 

[Tom Urtis]

Starting in row 1 of an unused sheet, in column A or whatever column you want, enter the formula
=CHAR(ROW())
and copy down to row 255.

Just so you know, I had no idea you only had 5% of them with problems. I assumed 100% had problems so that hung us up earlier. Anyway, problem's solved.

 

[Ekim]

Out of curiousity... where do you find those character values in case I ever need them in the future which I'm sure I will.

One of the easiest ways of determining the characters in a cell is to use Chip Pearson’s “Cell View” add-in. The add-in allows you to see the exact characters in a cell (including non-printable characters), as well as their character codes.
http://www.cpearson.com/excel/CellView.htm

When you install this add in, it will add an item to your View menu called "View Cell Contents". Select a cell then click the menu item to display a dialog box that shows the cell’s contents, together with the character codes.

Regards,

Mike