Delete space and restrict text length

[dkalra1981]

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

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