cleaning up a field

[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

 

[Don Guillett]

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

 

[btadams]

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)