Trim In Place for Excel 2

[gall3on]

I would like to know how to do a Trim in place for excel, instead of having to do it from a reference cell/column. I don't want the cell content to look like =TRIM(A1).

 

[Welshbird]

copy > paste special > values afterwards?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[gall3on]

That would require me to have a copy of the columns somewhere else. I'm having to do this to multiple columns with plenty of row data as it is. Is there another way of doing this without having to send a big-sized file?

 

[xlbo]

Find & Replace " " with ""

Otherwise you need code or a formula

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[macropod]

Hi Gall3on,

Try the following macro. It deletes leading and trailing spaces from values, but not formulae, in the selection. Non-breaking space characters (CHR(160)) are also processed, by changing them first to normal spaces. The macro also reduces internal spaces to a single space. Use Trim(Cell.Value) instead of Application.Trim(Cell.Value) to retain the original internal spacing.

Sub TrimRange()
Dim Cell As Range
'Also Treat CHR 0160, as a space (CHR 032)
Selection.Replace What:=Chr(160), Replacement:=Chr(32)
	For Each Cell In Selection.SpecialCells(xlConstants)
		Cell.Value = Application.Trim(Cell.Value) ' Delete "Application." to maintain any internal double-spacing
	Next Cell
MsgBox "Finished trimming " & vbCrLf & "excess spaces", 64 
End Sub

Cheers

[MS MVP - Word]

 

[KenWright]

I use Dave McRitchie's Trimall macro to do everything in situ, and wouldn't be without it now. Trims and cleans.

Sub a_TrimALL()
'David McRitchie 2000-07-03 mod 2000-08-16
    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

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



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

 

[KenWright]

Also, if you do this a lot then you should set up the code and assign it to a keyboard shortcut so you can do it with a couple of keypresses.

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

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