TRIM function

[Chimelle]

Greetings!

I'm using Excel 2007 and would like to apply the TRIM funtion to the entire
spreadsheet. I know how to do it for a given cell and then copy it to the
rest of the column but would like to do a whole spreadsheet at the same time
if possible.

Thanks!

 

[Bob Phillips]

Sub TrimData()

For each cell In Activesheet.usedrange

cell.Value = Trim(cell.Value)
Next cell
End Sub

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bernard Liengme]

1) On Sheet2 in A1, enter =TRIM(Sheet1!A1)
2) Copy this as far to the right and down as needed to capture all used
cells on Sheet1
3) Select all of Sheet2 used cells (CTRL+A or click the box where column
and row headers meet (to the left of A; above 1)
4) Use Copy
5) With the cells still selected use Edit | Paste Special with Values
checked
6) Now Sheet2 has values and Sheet1 could be deleted - but do double check
first.
best wishes

 

[Rick Rothstein \(MVP - VB\)]

That might not do exactly what the OP asked for. The VBA Trim function
**only** removes spaces from the left and right sides of the text... the
worksheet TRIM function collapses multiple internal spaces down to single
spaces as well. You could modify your code like this (if you want to remain
wholly within VBA) to match this functionality....

Sub TrimData()
Dim CellText As String
Dim Cell As Range
For each Cell In Activesheet.UsedRange
CellText = Cell.Value
CellText = Trim(CellText)
Do While InStr(CellText, " ")
CellText = Replace(CellText, " ", " ")
Loop
Next
Cell.Value = CellText
End Sub

or, if you don't mind reaching out to the worksheet world, like this...

Sub TrimData()
Dim Cell As Range
For each Cell In Activesheet.UsedRange
Cell.Value = Application.WorksheetFunction.Trim(Cell.Value)
Next
End Sub

Rick

 

[Bob Phillips]

but then it again it might.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

True (which I implied by saying it might not)... all I was trying to do was
alert the OP to the possibility that it might not do what was anticipated of
it if the data contained multiple internal spaces (and to offer a work
around if one was needed).

Rick