Any Excel Experts Out There? 2

[Bremmerz]

I have an imported csv file 16000 records in it. One of the fields has a format that looks like this:

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

I would like to be able the export the information 'SMTP:John.Smith@domain.com' from the each of these 16,000 records. The name John.Smith is different for every record, but '@domain.com' remains persistent.

Any ideas on how I can do this?

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 )

 

[Zathras]

I didn't want to assume that all of the domains were .com, so I assumed all of the addresses could be found between [blue]

 %SMTP:

[/color] and [blue]

 %X400

[/color]

If that is indeed the case, then this formula should give you what you want:
[blue]

=MID(A1,FIND("%SMTP:",A1)+6,FIND("%X400",A1)-FIND("%SMTP:",A1)-6)

[/color]

 

[SkipVought]

Hi,

Here's a formula where A14 is a source data cell...

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

hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Zathras]

Ah, I missed the point that you wanted to include the "SMTP:" in the result. In that case, my version should be revised to:
[blue]

=MID(A1,FIND("%SMTP:",A1)+1,FIND("%X400",A1)-FIND("%SMTP:",A1)-6)

[/color]

 

[Bremmerz]

Guys,

If you where female I'd kiss u! But you'll just have to make-do with a manly handshake, a shiny purple star and a MASSIVE THANK YOU!



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 )