Find text in a cell and copy text to another cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
With your text in A1, try this in B1

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

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


(e-mail address removed)
 
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.
Click to expand...

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
 
try
=MID(A3,SEARCH("p:",A3)+2,FIND("%",A3)-SEARCH("p",A3)-2)
to get
Click to expand...

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
 
Hello All,

Many thanks for all your timely and brillant answers the first and last
especially.
 
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)
 
Back
Top