Use the REPLACE method

Excel Help for Use The Replace Method in Automating Data Analysis Tasks

Forum Topic

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

View Full Post

Find relevant Excel templates and add-ins for Use the REPLACE method in the Excel Business Solutions Directory