extracting values inside a cell

[kwillkom]

Hi,

Can you assist with a formula that will extract values (such as names or distribution lists names) contained within a cell. Below is sample data i would like to extract the person's name or the distribution list name. This file was obtained by exporting the members of a DL from MSOutlook address book.

Cell A1: CN=Baird\, Jonathan R.,OU=Santa Ana-114 E 5th,OU=CA,OU=Users,OU=FA Title,DC=corp,DC=firstam,DC=com</SPAN>

Cell A2: CN=Truuvert\, Paavo R.,OU=Santa Ana-421 N Main,OU=CA,OU=Users,OU=FA Title,DC=corp,DC=firstam,DC=com</SPAN>

Cell A3:</SPAN>

</SPAN> CN=FAHQ-DL-Server Operations Management,OU=Distribution Lists,OU=Exchange Distribution List & Custom Recipients,OU=HQ,DC=corp,DC=firstam,DC=com</SPAN> <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

</SPAN> If the formula can get rid of the "\" that would be great too but note not all records have the "\", if not no worries. I am using MSExcel 2007. Thank you in advance for your assistance!

</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[CodeNinja]

=MID(A1,4,FIND(",OU=",A1)-4)

Copy down...

 

[Rick Rothstein]

If the "OU=" always follows the comma after the text you want to retrieve, then use CodeNinja's formula, but modify it like this (to get rid of the backslash as well)...

=SUBSTITUTE(MID(A1,4,FIND(",OU=",A1)-4),"\","")

Otherwise, if "OU=" does not always follow, then this formula should work...

=SUBSTITUTE(MID(LEFT(SUBSTITUTE(A1,"\,","\"),FIND(",",SUBSTITUTE(A1,"\,","\")&",")-1),4,99),"\",",")