Find text in a cell and copy text to another cell

[Shaun]

In an excel cell I have the text -

blah:SMTP:[email protected]%X400:c=GB;a=Gold 400;p=XXX
DRT;s=mslaters;%X500:/o=DFT/ou=DFTU/cn=Recipients/cn=mslatertest

I then want to copy the text after SMTP: in the cell but before the % into
an adjacent cell
I have used the function - =MID(K2,FIND("SMTP:",K2)+5,40) to copy upto 40
characters, so the result is SMTP:[email protected]%X400:c= etc but how can I then
truncate this or am I on the wrong solution.

Thanks in Advance, Shaun

 

[Mike H]

With your text in A1, try this in B1

=MID(A1,(FIND("SMTP",A1)+5),(FIND("%",A1))-(FIND("SMTP",A1)+5))

Mike

 

[Don Guillett]

try
=MID(A3,SEARCH("p:",A3)+2,FIND("%",A3)-SEARCH("p",A3)-2)
to get


(e-mail address removed)

 

[Rick Rothstein \(MVP - VB\)]

In an excel cell I have the text -

blah:SMTP:[email protected]%X400:c=GB;a=Gold 400;p=XXX
DRT;s=mslaters;%X500:/o=DFT/ou=DFTU/cn=Recipients/cn=mslatertest

I then want to copy the text after SMTP: in the cell but before the % into
an adjacent cell
I have used the function - =MID(K2,FIND("SMTP:",K2)+5,40) to copy upto 40
characters, so the result is SMTP:[email protected]%X400:c= etc but how can I
then
truncate this or am I on the wrong solution.

Another possibility...

=SUBSTITUTE(LEFT(A1,FIND("%",A1)-1),LEFT(A1,FIND("SMTP",A1)+4),"")

where the 4 in the +4 is the length of the SMTP string being found in the
2nd LEFT function call.

Rick

 

[Rick Rothstein \(MVP - VB\)]

try

=MID(A3,SEARCH("p:",A3)+2,FIND("%",A3)-SEARCH("p",A3)-2)
to get

That won't work if the "blah" at the beginning of the string were short for
"blah-blah-blah" (meaning some piece of unknown text) and that piece of
unknown text contained a "p" in it.

Rick

 

[Shaun]

Hello All,

Many thanks for all your timely and brillant answers the first and last
especially.

 

[Don Guillett]

Adding my forgotten : in the second search should work for any text without
p:
AND, will compensate for upper/lower by using SEARCH instead of FIND
=MID(A3,SEARCH("p:",A3)+2,FIND("%",A3)-SEARCH("p:",A3)-2)