how to trim trailing spaces?

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.
LVL 3
dandeliondreamAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dlmilleCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
torakeshbCommented:
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.
dlmilleCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

p912sCommented:
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
broro183Commented:
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
dandeliondreamAuthor Commented:
thanks guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.