Please Help "Formula or Macro

[Deron]

I was wondering if someone could help, I am trying to
devise a way to get particular data out of an excel Cell.
This is a copy of the data that varies


1st Cell Example
X400:c=US;a= ;p=mycompany ;o=TX005;s=MENDOZA;g=GEORGE;%
CAD:E001310%SMTP:[email protected]

2nd Cell Example
CAD:194412%SMTP:[email protected]%
X400:c=US;a= ;p=mycompany o=TX005;s=KURNIK;g=JOANN;

I want to grab just the SMTP Addresses from this
spreadsheet. That is all I want. Anyone have any ideas

 

[Guest]

H

Assuming that the data starts in cell A1 then put the following formula in the required cell and copy down. It also makes the assumption that the string %X400 will always follow immeditely after the end of the address. If this is not so, then you will have to modify the way that the end of the address is found. This should give you the idea anyway

Ton

=IF(ISERROR(SEARCH("%X400",A1,SEARCH("SMTP:",A1,1))),MID(A1,SEARCH("SMTP:",A1,1)+5,LEN(A1)),MID(A1,SEARCH("SMTP:",A1,1)+5,SEARCH("%X400",A1,SEARCH("SMTP:",A1,1))-SEARCH("SMTP:",A1,1)-5)

----- Deron wrote: ----

I was wondering if someone could help, I am trying to
devise a way to get particular data out of an excel Cell.
This is a copy of the data that varies


1st Cell Exampl
X400:c=US;a= ;p=mycompany ;o=TX005;s=MENDOZA;g=GEORGE;
CAD:E001310%SMTP:[email protected]

2nd Cell Exampl
CAD:194412%SMTP:[email protected]
X400:c=US;a= ;p=mycompany o=TX005;s=KURNIK;g=JOANN

I want to grab just the SMTP Addresses from this
spreadsheet. That is all I want. Anyone have any idea

 

[Guest]

Deron,
It appears that you have two different formats, one with the address in the middle followed by %, and another with the address at the end followed by nothing. I came up with two formulas:

1st case:
=MID(A1,FIND("SMTP:",A1)+5,FIND("%",A1,FIND("SMTP:",A1))-FIND("SMTP:",A1)-5)

2nd case:
=MID(A1,FIND("SMTP:",A1)+5,LEN(A1)-FIND("SMTP:",A1)-4)

And then assuming the second case will always start with CAD, you can put the above two formulas together:

=IF(LEFT(A1,3)="CAD",MID(A1,FIND("SMTP:",A1)+5,FIND("%",A1,FIND("SMTP:",A1))-FIND("SMTP:",A1)-5),MID(A1,FIND("SMTP:",A1)+5,LEN(A1)-FIND("SMTP:",A1)-4))

It's pretty messy looking but it does the job.

Good Luck,
Mark Graesser
(e-mail address removed)

----- Deron wrote: -----

I was wondering if someone could help, I am trying to
devise a way to get particular data out of an excel Cell.
This is a copy of the data that varies


1st Cell Example
X400:c=US;a= ;p=mycompany ;o=TX005;s=MENDOZA;g=GEORGE;%
CAD:E001310%SMTP:[email protected]

2nd Cell Example
CAD:194412%SMTP:[email protected]%
X400:c=US;a= ;p=mycompany o=TX005;s=KURNIK;g=JOANN;

I want to grab just the SMTP Addresses from this
spreadsheet. That is all I want. Anyone have any ideas