Extract Partial Cell Data

P

pjw23

I am hoping someone out there can provide some insite on this...

I need a way to extract partial data strings from a cell and move i
into another cell. The data string that I need always starts and end
with the same characters, however the middle is unique.

----EXAMPLE----
|Start| Middle | End |Other Data
SMTP:[email protected]%x400whatever

----End Example----

I can't use "text to colunm" because it creates a bigger mess. Sinc
the data I need isn't always the start of the complete string.

Please let me know if anyone has any creative ideas. Thank you i
advance
 
F

Frank Kabel

Hi
and what do you want to extract?. The email address
(always ending with '.com')?
if yes try
=TRIM(MID(A1,FIND("SMTP:",A1)+5,FIND(".com",A1)-FIND
("SMTP:",A1)-1))
 
P

pjw23

Frank, Thank you very much for the reply...

I forgot to mention one critical piece. I need to remove the SMTP
info from the data string. This way there are no SMTP addresses in th
string. I need the string to stay exactly like it is minus the SMT
info. I appreciate all of your help. Thanks
 
P

pjw23

Can't seem to get it to do what I need...Here is an example of what I a
trying to do

Data from cell A1
%CCMAIL:ASmith,Amy at CLT%SMTP:[email protected]%X400:c=US;a
;p=XYZ_CO;o=CLT;s=Smith;g=Amy;

Formula
=TRIM(MID(A2,FIND("SMTP:",A2)+5,1024))

Formula Results
[email protected]%X400:c=US;a= ;p=XYZ_CO;o=CLT;s=Smith;g=Amy;

Disired Result
%CCMAIL:ASmith,Amy at CLT%X400:c=US;a= ;p=XYZ_CO;o=CLT;s=Smith;g=Amy;

I need everything before "SMTP:" to remain unchanged, and everythin
after "@xyz.com%" to remain unchanged. The SMTP address needs to b
removed. (Including SMTP in the beginning and the % at the end) Th
problem is that the SMTP is never in the same spot in the string.
Sometimes it's the first e-mail, sometimes it's a middle email, an
sometimes it is an ending email. I hope this helps, otherwise I hav
to update a couple thousand of these by hand...(UGH!)

Thanks again for all the help..
 
P

Peo Sjoblom

One possible way

=SUBSTITUTE(A1,MID(A1,FIND("SMTP",A1),FIND("X400",A1)-FIND("SMTP",A1)),"")
 
F

Frank Kabel

Hi
try
=SUBSTITUTE(A1,MID(A1,FIND("SMTP:",A1)+5,FIND(".com",A1)-FIND("SMTP:",A
1)-1),"")
 
Top