I am having problems with cleaning my data. Currently I am importing data into excel and run a macro that resizes columns, deletes other columns I don't need, and arranges the data in the format I need. I then copy the results and dump it into another spreadsheet that I then used to perform various calculations. However, recent I have noticed that several of my formulas aren't calculating. After some review I noticed that there are extra spaces in the data, so for example I will have IN123456789__, where the under scores are actually spaces. I want to remove all leading and trailing spaces or hidden characters from this data as it causes problems with my formulas.
I have tired the trim formula, which works until I delete the original data column, I can leave the column and then rewrite the macro to only copy the new trimmed data columns, but that seems like wasted effort. Is there another way to accomplish this automatically via VB?
I have attached a copy of the source data so you can see the extra spaces.