I have an excel worksheet. A user enters data in a cell. But he can enter the
data with leading and traliling spaces. So after the user enters data in the
cell, I want to attach like TRIM function to remove the spaces in the same
cell. Can some one help me how to do this?
Thank you

In the same cell?

You would need event code to do that as the data is entered.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("A1:A20"), Target) Is Nothing Then
On Error GoTo endit
Application.EnableEvents = False

If (Not IsEmpty(Target)) And _
Not IsNumeric(Target.Value) And _
InStr(Target.Formula, "=") = 0 _
Then Target.Value = Application.Trim(Target.Value)
End If
endit:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

On Wed, 31 Oct 2007 14:23:00 -0700, Ram wrote:

I have an excel worksheet. A user enters data in a cell. But he can enter the
data with leading and traliling spaces. So after the user enters data in the
cell, I want to attach like TRIM function to remove the spaces in the same
cell. Can some one help me how to do this?
Thank you

To do so would require an event macro. Put this in your worksheet code
module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rArea As Range
Dim rCell As Range
On Error GoTo ErrorOrExit
Application.EnableEvents = False
For Each rArea In Target
For Each rCell In rArea
With rCell
If Not .HasFormula Then _
If Not IsNumeric(.Value) Then _
.Value = Trim(.Value)
End With
Next rCell
Next rArea
ErrorOrExit:
Application.EnableEvents = True
End Sub



But one can usually work around the problem by using TRIM() in any
formulas that reference the cell.

In article ,
Ram wrote:

I have an excel worksheet. A user enters data in a cell. But he can enter the
data with leading and traliling spaces. So after the user enters data in the
cell, I want to attach like TRIM function to remove the spaces in the same
cell. Can some one help me how to do this?
Thank you