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.

[TABLE="width: 1067"]
<TBODY>[TR]
[TD]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>[/TD]
[/TR]
[TR]
[TD]
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>[/TD]
[/TR]
[TR]
[TD]
Cell A3:</SPAN>[/TD]
[/TR]
[TR]
[TD]</SPAN>[TABLE="width: 1067"]
<TBODY>[TR]
[TD]CN=FAHQ-DL-Server Operations Management,OU=Distribution Lists,OU=Exchange Distribution List & Custom Recipients,OU=HQ,DC=corp,DC=firstam,DC=com</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]</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![/TD]
[/TR]
[TR]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

 

[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),"\",",")