Solved

how to trim trailing spaces?

Posted on 2012-03-23
6
371 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
[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
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 7

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Cancel future meetings from user mailboxes in Office 365 using Remove-CalendarEvents
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

617 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