Macro to delete spaces at end of a Cell

[Sponge14]

I have a spreadsheet that starts out with an employee name, followed by their employee ID in ( ) all in one cell, and then in the adjacent cell, the # of calls they took:
A B
John Doe (e123456) 30
Jane Doe (e456789) 40

I have a working macro that simply adds a column between A and B, then runs a Text to Columns with the ( as the delimter so it ends up:
A B C
John Doe e123456) 30
Jane Doe e456789) 40

Now the problem is my VLOOKUP on another worksheet looks for the name in column A and reports back with the # of calls in C. However, after the text to columns runs, it leaves a Space at the end of the name which causes VLOOKUP to not work.

I have tried to create a macro that will uses =TRIM and overwrites the cell with the result. The space remover macro takes the macro that removes the space in one cell, and repeats that over and over.

I have this spreadsheet saved as Stats Master.xls. The problem occurs when I take the master and save it to the Date: Stats April 28.xls. The text to columns macro continues to work, however, the space remover stops working because it says that it's referencing the macro that does the =TRIM in one cell as 'Stats Master.xls !SpaceRemove'.

So does anyone have a macro that will remove the spaces at the end of all cells in A? Is there an easier way to do this that I am just missing?

[Sponge14]

Also, the space between the first and last names needs to stay...

[Sponge14]

Wow, I typed all that out and think I got it figured out from another post on another site I missed when I searched earlier....

It looks like this has worked:

Please Login or Register  to view this content.

[ncmay]

Do you have CHAR(160) in the cell?
http://www.excelforum.com/excel-prog...har-160-a.html
http://www.rondebruin.nl/clean.htm

Norman

[Pichingualas]

This should work:

Please Login or Register  to view this content.

I hope that helps.

[Pichingualas]

Haha yes Sponge that should be working too. Too bad I didn't refresh earlier instead of typing lol