Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

trailing spaces all columns

Posted on 2016-10-10
4
Medium Priority
?
126 Views
Last Modified: 2016-10-11
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.
0
Comment
Question by:pma111
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 49

Accepted Solution

by:
Martin Liss earned 1000 total points
ID: 41837118
Here's a macro you can use.

Sub TrimAll()
Dim cel As Range
For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
    cel = WorksheetFunction.Trim(cel)
Next
End Sub

Open in new window

1
 
LVL 27

Assisted Solution

by:Glenn Ray
Glenn Ray earned 500 total points
ID: 41837125
I use a third-party application to clean up ranges of data like that (ASAP Utilities).  But, here is a macro that will do the same.  You just highlight the range of data to clean up and run the macro.

Sub ReEnter()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng As Range
    Dim c As Object
    Set rng = Selection
    For Each c In rng
        c.Formula = Trim(c.Formula)
    Next c
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Open in new window


-Glenn
0
 
LVL 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41837155
Here is another one which will be faster to finish the task.
Sub RemoveTrailingSpaces()
Dim arr
Dim i As Long, j As Long
On Error Resume Next
arr = ActiveSheet.UsedRange.Value
For i = 1 To UBound(arr, 1)
   For j = 1 To UBound(arr, 2)
      arr(i, j) = Trim(arr(i, j))
   Next j
Next i
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
End Sub

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41838266
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.
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Microsoft has changed the look and feel of Azure AD and Microsoft account sign-in pages so that you will have a more unified look and feel when moving between the two interfaces.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

660 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question