Stripping out unwanted date in cell

  • Thread starter Thread starter Jim Kiddoo
  • Start date Start date
J

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
 
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.
 
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
 
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))
 
But we all admire your tenacity.
<vbg>

J.E. McGimpsey said:
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))
Click to expand...
 
That worked perfectly! Thank you very much, you have made my life much
easier. Thanks Again!
Sincerely
Jim Kiddoo
 
Back
Top