How to remove spaces before and after data in Excel

Back when I used Excel 2003, I learned about an application called Trim Spaces by Able Bits that would trim any spaces before and after a number or text data in the column that I was working with. It was easy, with a click of a button BAM! all the spaces were gone.

Now that I'm at ASU - working with databases and performing some cleanup using Excel 2010, I figured it would be prudent to download Trim Spaces once again. It was incredibly easy to find, using the online search function now built into Excel and after just a few mere clicks I downloaded and installed the application.

Unfortunately, it doesn't seem to work with my 64 Bit version of Excel 2010 and I'm not sure why. From what I gather, it should work for the 64Bit version of Excel and Windows 7. I tried AbleBits FAQ and even tried downloading the "Microsoft Visual Studio 2010 Tools for Office Runtime" that I found as a solution on a Microsoft support forum, but nothing seemed to work.

Well, from there I decided to see what I could do within the confines of the Excel program. It seemed like there were two options:

1. Use FIND and REPLACE
2. Use a Macro

I looked over FIND and REPLACE first and found an About.com article by James Marshall and discovered that I could use the [(foo) and (bar)] wildcards to remove the extra spaces before and after by data. "This would be awesome! I could remove all extra spaces from an entire workbook in one swoop!" I thought...and then promptly found out that Microsoft recently nerfed the wildcards available for use in with FIND and REPLACE. We are now restricted to ~, * and ?

So that option was out.

So I decided to bite the bullet and just write out a simple macro. Now I'm sharing it with you, dear reader, in hopes that it gets to you first before you do the 5 hours of searching and fact finding that I ended up having to do today.

Both of these macros create a new column and use excel's built in TRIM function. After you run it, you can just simply copy and paste the values into your original column - voila! extra spaces are gone!

Macro 1: Trim spaces - consecutive

This macro runs in a column as long as the cell in the next row has data. If you have any blanks in your column, the macro will automatically stop. Copy and paste this into your Excel VBA panel:

Loading ....

Macro 2: Trim Spaces by Number of Rows

This macro runs in a column for the number of rows that you specify. IIt will just run right on past any blanks until it reaches its end count. Copy and paste this into your Excel VBA panel:

Loading ....