Auto-Trim through validation or the macro
 

Dear Friends

I have a date with consisting of more than 1200 row and 15 columns;
When I use Pivot Table for this data; I use Trim () function by
linking in a additional worksheet and later on Special paste value
over original data to remove unnecessary spaces

My question is that is there any possibility to use auto Trim Function
through Validation or any other means. OR it is possible to create a
short through macro to use this trim function.

Looking for your help.

Auto-Trim through validation or the macro
 

Data Validation:

Choose your cells (in this example, the active cell will be A1), the choose Data / Validation....
on the Settings tab, under Allow: choose Custom, then use the formula =A1=TRIM(A1)

You can set a message to say that they entry must not include extra spaces.

You can also use the change event: Copy this code, right-click the sheet tab, select "View Code" and
paste the code into the window that appears.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
On Error Resume Next
Application.EnableEvents = False
If Not Target.HasFormula Then _
Target.Value = Application.WorksheetFunction.Trim(Target.Value)
Application.EnableEvents = True
End Sub


--
HTH,
Bernie
MS Excel MVP


"SG" wrote in message
ups.com...
Dear Friends

I have a date with consisting of more than 1200 row and 15 columns;
When I use Pivot Table for this data; I use Trim () function by
linking in a additional worksheet and later on Special paste value
over original data to remove unnecessary spaces

My question is that is there any possibility to use auto Trim Function
through Validation or any other means. OR it is possible to create a
short through macro to use this trim function.

Looking for your help.