Link to home
Start Free TrialLog in
Avatar of Quickstepbr
QuickstepbrFlag for Brazil

asked on

Find and delete text with VBA

Importing data from a web site I've got a lot of unwanted text in the e-mail column.
How can I clear all the junk after the e-mail adress in this column?

Thank you very much,

Avatar of GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Did you intend to attach an example file of some sort? It would help.
Avatar of Quickstepbr


Sorry GrahamSkan. I thought i did.
Which email column?  There are two of them...
Column H
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Worked just fine.

I adapted your code to clean-up column E, too.

sub CleanUp()
Dim lngLastRow As Long
Dim lngIndex As Long
Dim intPos As Integer
Const QUOTE = "- "
lngLastRow = Range("e65536").End(xlUp).Row

For lngIndex = 2 To lngLastRow
    intPos = InStr(1, Range("e" & lngIndex).Value, QUOTE)
    If intPos > 0 Then
        Range("e" & lngIndex).Value = Left$(Range("e" & lngIndex).Value, intPos - 1)
    End If

End Sub

Thank you very much.
You're welcome and I'm glad I was able to help.

Marty - MVP 2009 to 2012