Solved

how to trim trailing spaces?

Posted on 2012-03-23
6
366 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 42

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 42

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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

808 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