Exclude Space and restrict text length

Excel Help for Exclude Space And Restrict Text Length in Automating Data Analysis Tasks

Exclude Space And Restrict Text Length

Rate this: (3/5 from 1 vote)

	Hi  I want to delete spaces and need to restrict the text length (e.g +441612890000) in a worksheet. I tried to use Data Validation, but I have noticed that if you copy and paste the data from a different worksheet then the validation doesn't works. I tried adding code VBA Code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.CutCopyMode = False End Sub and Private Sub Worksheet_Change(ByVal Target As Range)     If target.value meets your_conditions Then         Exit Sub     Else         Target.Value = ""         MsgBox "You can't paste that here."     End If     End Sub but nothing works.  Is there a way we can develop a button which will delete all the spaces once the data is copied from a different worksheet or can we write a script in the VBE to resolve this? I would be pleased if someone could help me to sort this out. Many Thanks Dee
	Dee
	Posted by dkalra1981 on 27 Apr 2010

Replies - Displaying 1 to 1 of 1

Order Replies By: Most Recent | Chronological | Highest Rated

	Use the REPLACE method Rate this: (3/5 from 1 vote) You could use a simple routine to run replace methods and reduce the length of the copied data. VBA Code: Sub cleanup() Dim cl As Variant Dim tmpstr As String For Each cl In Selection     tmpstr = cl.Value     tmpstr = Replace(tmpstr, " ", "")     tmpstr = Left(tmpstr, 10)     cl.Formula = tmpstr Next End Sub You may want to add an If statement to test for cell formatting and/or other tests and transformations as needed.
	Excel Business Forums Administrator
	Posted by Excel Helper on 27 Apr 2010

Displaying page 1 of 1