Solved

how to trim trailing spaces?

Posted on 2012-03-23
6
363 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
ID: 37760108
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
ID: 37760109
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
ID: 37760119
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Assisted Solution

by:p912s
p912s earned 100 total points
ID: 37761753
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
ID: 37762531
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
ID: 37764276
thanks guys!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Outlook Free & Paid Tools
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

920 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

15 Experts available now in Live!

Get 1:1 Help Now