Stripping out unwanted date in cell

[Jim Kiddoo]

Hello,
I am trying to import a Global address list, but my problem is I need just
the smtp address in the cell so i want to strip out all the other junk, here
is a couple of examples of what the field could appear like. I need to strip
out everything accept for the SMTP:[email protected]. It
could be any extension and it is not always a percent at the beginning or
end.
SMTP:[email protected]%MS:UOF9/UNIVERSITY/bcairn%X400:c=US;a=;p=Uof9;o=Univ
ersity?Hall;s=Cairn;g=bubba;%CCMAIL:Cairn, bubba at University_Hall

CCMAIL:ssdsexam at
STUDENT-SERV%MS:UOF9/STUDENTSER/SSDSEXAM%SMTP:[email protected]%X4
00:c=US;a= ;p=Uof9;o=STUDENT-SERV;s=ssdsexam;

Any help is much appreciated.

Sincerely

Jim Kiddoo

 

[J.E. McGimpsey]

One way (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):


=MID(A1,FIND("SMTP",A1),MIN(IF(ISERR(FIND({"%",":",";","$"},A1,FIND("
SMTP",A1)+5)),"",FIND({"%",":",";","$"},A1,FIND("SMTP",A1)+5)))-FIND(
"SMTP",A1))

Add any other delimiters to both constant arrays, {...}'s, in the
same order.

 

[Jim Kiddoo]

Thanks very much, works perfectly unless the smtp address is tha last in the
string, with nothing after it. Any suggestions?
Thanks again
Sincerely
Jim Kiddoo

 

[J.E. McGimpsey]

This has gotten convoluted enough to make me think there's a better
way, but this will work (again, array-entered):

=MID(A1,FIND("SMTP",A1), MIN(IF(ISERR(FIND({"%",":",";","$"}, A1,
FIND("SMTP",A1)+5)), "", FIND({"%",":",";","$"} , A1,
FIND("SMTP",A1)+5))) + ((LEN(A1)+1) * ISERR(FIND({"%",":",";","$"},
A1, FIND("SMTP",A1)+5))) - FIND("SMTP", A1))

 

[Dave Peterson]

But we all admire your tenacity.
<vbg>

This has gotten convoluted enough to make me think there's a better
way, but this will work (again, array-entered):

=MID(A1,FIND("SMTP",A1), MIN(IF(ISERR(FIND({"%",":",";","$"}, A1,
FIND("SMTP",A1)+5)), "", FIND({"%",":",";","$"} , A1,
FIND("SMTP",A1)+5))) + ((LEN(A1)+1) * ISERR(FIND({"%",":",";","$"},
A1, FIND("SMTP",A1)+5))) - FIND("SMTP", A1))

 

[Jim Kiddoo]

That worked perfectly! Thank you very much, you have made my life much
easier. Thanks Again!
Sincerely
Jim Kiddoo