Address Book Imports

[Bremmerz]

I've been sent an address book export from one of our of sister companies.

Unfortunately they have exported all of the e-mail address (x400, MRS, etc) as well as the SMTP address. Does anybody know a formula I could use in excel to extract only the SMTP information from the e-mail addresses field?

An examples from the export if they help...

"SMTP:John.Smith@domain.com%SNADSOMAIN/SMITHJO%X400:c=COM;a=DBP;p=ABC;o=COMPANY;s=Smith;g=John;i=AK;%MRS:John.Smith@MRSIN.domain.com"

I originally thought of removing the " as the text qualifier and importing the txt file in excel, using % as another deliminater, but not all records are exported as SMTP, SNADS, X400, MRS.

I've seen the SMTP address be extracted before in excel but I can't remember how it was done

The export holds 16,000+ records so any help is appreciated

TIA,

Kris


If someone annoys you, walk a mile in their shoes. That way you'll be a mile away from them, and you'll have their shoes )

 

[Bremmerz]

A couple of guys answered this in the MS Office forum. If this has been a problem for anyone else, the formula in excel is:

=MID(A14,FIND("SMTP:",A14),(FIND("%",A14,FIND("SMTP:",A14))-FIND("SMTP:",A14)))

Substitute A14 for the field that the 'e-mail addresses' data is held

If someone annoys you, walk a mile in their shoes. That way you'll be a mile away from them, and you'll have their shoes )