Excel Quickie

[Django x2]

Got a fairly large sheet circa 9k rows. In one column, each cell lists all the users email addresses assigned to them. I need to find a way to trim that down to the one email address I want shown in each cell. For instance, the cell may read like this:

X400:C=us\;A= \;P=DOMAIN\;O=Exchange\;S=Jones\;G=John\;;SMTP:[email protected];smtp:[email protected];X500:/o=DOMAIN/ou=Networks/cn=Recipients/cn=Jone26J;smtp:[email protected]

Basically, for each cell in that column, I want to strip out all of the rubbish data and just be left with the X500 part.

I thought about custom importing the CSV and choosing where to place the comma in that cell, but of course, each email address is different in length, etc.

Does that make sense? If so, what's the best way to do it? I'm guessing something like a complicated mid string lookup.

 

[Django x2]

Thanks dude,

Will that output for all 9000 rows?

 

[Django x2]

Thanks,

Just tried this and it returns everything back except the X500 address

 

[Django x2]

Excel 2013. In the return of my example, I get:

X400:C=us\;A= \;P=DOMAIN\;O=Exchange\;S=Jones\;G=John\;;SMTP:[email protected];smtp:[email protected];X500:/o=DOMAIN/ou=Networks/cn=Recipients/cn=Jone26J;smtp:[email protected]

Becomes:
[email protected]

Everything else is stripped out

 

[Django x2]

Linkex's code gives me the X500 email, eg [email protected]

Try breaking it up into sections, eg

Cell A1: X400:C=us\;A= \;P=DOMAIN\;O=Exchange\;S=Jones\;G=John\;;SMTP:[email protected];smtp:[email protected];X500:/o=DOMAIN/ou=Networks/cn=Recipients/cn=Jone26J;smtp:[email protected]

Cell B1: =SEARCH("X500",A1)

Cell C1: =MID(A1,B1,LEN(A1))

Cell D1: =SEARCH(":",C1,10)

Cell E1: =MID(C1,D1+1,LEN(A1))

Cell B1 should give the number of characters to start of 'X500' (eg 113 in this example)
Cell C1 should then be a string of text starting at X500
Cell D1 should give the number of characters in C1 to the start of first ':' (but skipping the one immediately after X500)
Cell E1 should then give you a string starting with the first data after the ':' found in D1


See if you can get that working and it might help see which bit of the code is going wrong.

Linkex has made quite a few assumptions about the data, so it's possible your actual data breaks one of the assumptions.

That's not the X500 address, that's the SMTP address. The X500 address is the part that reads:

X500:/o=DOMAIN/ou=Networks/cn=Recipients/cn=Jone26J;

 

[Django x2]

Yeah, that's what I'm after, the full X500 string, nothing before the X500 part, nothing after the semi colon (at the end of the X500 string)