Link to home
Start Free TrialLog in
Avatar of Pau Lo
Pau Lo

asked on

trailing spaces all columns

I have a sheet with loads columns, and I have just noticed, in all the cells of data - all the cell have endless trailing spaces. I had been creating new columns aside of each of the original columns, and using =trim(A2) to get rid of them, but its taking a long time to complete. Can anyone think of a quick way to remove all trailing spaces on all columns in the report in one single go? Or is my current technique the only way possible.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You could have carried on with the simple =TRIM(A2) method but go about it differently. Sounds like you are doing it one column at a time rather than doing it as a block.

Assuming your data is columns A to Z, in column AA put your =TRIM(A2) formula and copy across to column AZ. In other words just copy to the right until you have all columns of original data covered.

Select cells AA2 to AZ2 (or however far you needed to go right) and hover your cursor over the bottom right corner of AZ2 until it turns to a + sign. Double click and the formulas will be copied down until it reaches the last entry in column Z. You can then copy these new columns and rows and paste values over the original.