Solved

how to trim trailing spaces?

Posted on 2012-03-23
6
362 Views
Last Modified: 2012-03-25
Hi,

I have a excel file with about 5000 rows. How do i trim trailing spaces for example product code "APPLE      " to "APPLE" for a particular column without manually changing each one of them.
0
Comment
Question by:dandeliondream
6 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 200 total points
Comment Utility
The TRIM function removes all spaces in cells except for those separating two words.

You can add:

=TRIM(A2) and copy down if the data is in A2.

You can also do an EDIT->FIND->REPLACE to change " " to "" if you're not concerned with spaces separating words.  This acts on the selection if more than 1 cell, or you can select one cell and it will act on the whole sheet.

Otherwise, VBA can be used to perform these same functions.

The following trims all spaces in selected column.  The code is more sophisticated just to trim the right side, but please advise if this is needed, and I can code it for you.
E.g.,
Sub replaceSpaces()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim xCol As Long

    Set wks = ActiveSheet
    
    xCol = InputBox("Enter column to trim spaces", Default:=1)
    
    If IsNumeric(xCol) And xCol <> 0 Then
        wks.Columns(xCol).Replace what:=" ", replacement:="", lookat:=xlPart
    End If

End Sub

Open in new window


Please advise if further assistance is required.

Dave
0
 
LVL 6

Assisted Solution

by:torakeshb
torakeshb earned 100 total points
Comment Utility
if A1 contains the value "   test   ".. In B1 provide the command =trim(A1)... Double click B1 cell right - bottom (+ sign) to auto trim all the 5000 records.
0
 
LVL 41

Assisted Solution

by:dlmille
dlmille earned 200 total points
Comment Utility
The RTRIM function in VBA does exactly what you asked for - it trims spaces to the RIGHT of the cell's value.

This code will do the trick, you are prompted for the column and the macro does the rest:

Option Explicit

Sub trimSpacesOnTheRight()
Dim wkb As Workbook
Dim wks As Worksheet
Dim r As Range
Dim rng As Range
Dim xCol As Long
Dim lastRow As Long

    Set wks = ActiveSheet
    
    xCol = InputBox("Enter column to trim spaces on the right", Default:=1)
        
    lastRow = wks.Cells(wks.Rows.Count, xCol).Row
    
    Set rng = wks.Range(wks.Cells(1, xCol), wks.Cells(lastRow, xCol))
    
    For Each r In rng.SpecialCells(xlCellTypeConstants)
        r.Value = RTrim(r.Value)
    Next r

    MsgBox "Process Complete!"
End Sub

Open in new window


Just hit ALT-F11 and add a public module, paste this code in and run it.

See example workbook.

Dave
trimOnRight-r1.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Assisted Solution

by:p912s
p912s earned 100 total points
Comment Utility
As pointed out above you can use the Trim() function to trim off the spaces.

Make a copy of your spreadasheet before proceeding...

If your data is in A1, insert a new column B and in B1 enter =Trim(A1), copy that down as far as you need. Then select from B1 down to the bottom of your data so that only the cells with the formula in column B are selected, and copy, put your cursor in A1 and do a "paste special" only pasting values to overwrite the old data. Then you can delete column B.

HTH

Scot
0
 
LVL 10

Assisted Solution

by:broro183
broro183 earned 100 total points
Comment Utility
You may find it useful to also use the Clean function in conjunction with Trim, in case the characters that appear to be "spaces" also include any other non-printing characters.

hth
Rob
0
 
LVL 3

Author Closing Comment

by:dandeliondream
Comment Utility
thanks guys!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now