In an excel cell I have the text -

%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 %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

"Shaun" wrote:

In an excel cell I have the text -

%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 %X400:c= etc but how can I then
truncate this or am I on the wrong solution.

Thanks in Advance, Shaun

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




--
Don Guillett Excel MVP
SalesAid Software

"Shaun" wrote in message
...
In an excel cell I have the text -

%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 %X400:c= etc but how can I
then
truncate this or am I on the wrong solution.

Thanks in Advance, Shaun

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

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)



--
Don Guillett Excel MVP
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

In an excel cell I have the text -

%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 %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

Hello All,

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




"Rick Rothstein (MVP - VB)" wrote:

In an excel cell I have the text -

%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 %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