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:test.mail@ test.com%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 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


--
Don Guillett
SalesAid Software

"Alex Zaft" wrote in message
...
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:test.mail@ test.com%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 and nothing else.

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

Thanks much
Alex

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)


---
Message posted from http://www.ExcelForum.com/