cleaning up a field

  • Thread starter Thread starter Alex Zaft
  • Start date Start date
A

Alex Zaft

Hi All,
I'm using Excell 2003, i have file (about 3000 lines) that has a fied that
needs to be cleaned up.
Here's the example of the field:
"CCMAIL:test, test at PAIREMOTE%MS:TEST/PAIREMOTE/TEST%X400:c=GB;a=
;p=test;o=PAIREMOTE;s=test;g=test;%SMTP:[email protected]%X400:c=US;a=
;p=test;o=test;s=test;%RFAX:Biosepr@"
What i need is to clean out everything before and after the actual
SMTP address, so all the field has is (e-mail address removed) and nothing else.

Can someone please help? (as you can see i'm not that good with
Excel).

Thanks much
Alex
 
try
Sub findurl()
For Each c In Selection
x = InStr(1, c, "SMTP")
y = InStr(x, c, "com")
c.Offset(0, 1) = Mid(c, x + 5, y - x - 2)
Next
End Sub
 
Here's a non-macro solution. Copy/paste this formula into the next
column and change A6 to whatever cell is to the left

=MID(A6,FIND("SMTP",A6)+5,FIND(".com",A6)-FIND("SMTP",A6)-1)
 
Back
Top