trim entire column

[danmul]

Hi,
I have just received an excel file and I need to use the TRIM function on an entire column. I seem to be able to get it to work for a cell but not entire column. I know it's prob easy but I am new to this.
Thanks in advance,
Daniel.

 

[Griffyn]

If column A has the data you want to trim, then in cell B1, type in =TRIM(A1)

Copy that all the way down column B as far as necessary. Then copy column B and Paste Special, Values over column A. Then Delete column B.

 

[danmul]

Thanks!

 

[KenWright]

I always keep a copy of Dave McRitchies Trimall macro in my personal.xls. I use it any time I bring in external data:-

Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16 join.htm
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace what:=Chr(160), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next 'in case no text cells in selection
For Each cell In Intersect(Selection, _
Selection.SpecialCells(xlConstants, xlTextValues))
cell.Value = Application.Trim(cell.Value)
Next cell
On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Just select your range and run the macro.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------