Trim function

[SimonHughes]

Hello,

I am using Excel 2010

Is it possible to use the trim function in cell? I want to change what we see in A1 to what we see in A2 without using insert column > copy > paste special etc. Your help appreciated

<TABLE style="WIDTH: 71pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=94><COLGROUP><COL style="WIDTH: 23pt; mso-width-source: userset; mso-width-alt: 1097" width=30><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 23pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=30></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=64>A</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 23pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=30 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>. Mydata</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 23pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=17 width=30 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>Mydata</TD></TR></TBODY></TABLE>

 

[villy]

You can use it in cell but it depends on the use...

If you will use the function in A1 you should put the result of that function in different cell rather than A1 otherwise there will be error.

Trim function check in Excel Help...
It trims more than 1 spaces in your data even you apply trim nothing will happen since only 1 space is there..

 

[SimonHughes]

Many thanks but I still do not know how to trim in the active cell - can you help on that?

 

[moonfish]

You can do what you want to do with the substitute function:

=SUBSTITUTE(SUBSTITUTE(A1," ",""),".","")

 

[villy]

You can do what you want to do with the substitute function:

=SUBSTITUTE(SUBSTITUTE(A1," ",""),".","")

But still it is not possible to put the result in A1 itselt it should be in different cell

 

[SimonHughes]

That's correct - I need the solution to be done in the active cell

 

[moonfish]

You need a macro for that. Very simple one that works on the cell that's currently selected:

Sub ReplaceSpaces()
    ActiveCell.Replace What:=".", Replacement:=""
    ActiveCell.Replace What:=" ", Replacement:=""
End Sub

 

[VoG]

To trim a cell in situ you will need a macro

Sub ATrim()
With ActiveCell
    .Value = Trim(.Value)
End With
End Sub

 

[SimonHughes]

Thats great, both work well, many thanks. However, can these macro's be adapted to work on a range of cells?

 

[VoG]

Try

Sub ATrim()
Dim c As Range
For Each c In Selection
    c.Value = Trim(c.Value)
Next c
End Sub