Find text in a cell and copy text to another cell

S

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
 
M

Mike H

With your text in A1, try this in B1

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

Mike
 
D

Don Guillett

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


(e-mail address removed)
 
R

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
 
R

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
 
S

Shaun

Hello All,

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top