Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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

Find text in a cell and copy text to another cell
 

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