Home > Forum Home > Automating Data Analysis Tasks > Exclude Space and restrict text length Share

Exclude Space and restrict text length

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


Forum TopicPost Reply Login

Exclude Space And Restrict Text Length

Rate this:
(3/5 from 1 vote)
ConfusedHi 

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 on
 
Replies - Displaying 1 to 1 of 1Order Replies By: Most recent | Chronological | Highest Rated
Shocked
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 on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Exclude Space and restrict text length in the

© Business Spreadsheets   Legal   Follow us: