I would like to build a list within a single cell from a sting stored another cell. The string has a semi colon delimiter ';' to separate the string. I need to extract the CN=*, and remove the remainder, in the adjacent cell.
I've tried the following, but with little joy
=SUBSTITUTE(A87,";CN=",CHAR(10))
=SUBSTITUTE(A87,",",CHAR(10))
=SUBSTITUTE(A87,",",CHAR(10)&",CN=")
=SUBSTITUTE(A87,";",CHAR(10))
| String | Output |
|---|---|
| CN=User2,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User4,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User56,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User9,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Jane45 user,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User-Donna,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User76 smith,OU=blurb,OU=Test4,DC=Test,DC=Testal;CN=Pink Panther,OU=blurb,OU=Test,DC=Testing,DC=Testal;CN=Testuser78,OU=blurb,OU=Tester,DC=Test,DC=Testal;CN=great Scott,OU=blurb,OU=Test,DC=Test,DC=Local;CN=Leah Human,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Alan Desai,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Duff Beer,OU=Groups,OU=Test,DC=Test,DC=Testal;CN=Jane Doe,OU=Users,OU=Test76,DC=Test,DC=Testal;CN=simple user67,OU=Users,OU=Test,DC=Test,DC=Testal;CN=test O'Lord,OU=Users,OU=Test,DC=Concero,DC=Testal | CN=User2. CN=User4 CN=test O'Lord. CN=etc... |
| CN=User2,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User4,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User56,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=User9,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Jane45 user,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Pink Panther,OU=blurb,OU=Test,DC=Testing,DC=Testal;CN=Testuser78,OU=blurb,OU=Tester,DC=Test,DC=Testal;CN=great Scott,OU=blurb,OU=Test,DC=Test,DC=Local;CN=Leah Human,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Alan Desmond,OU=blurb,OU=Test,DC=Test,DC=Testal;CN=Duff Beer,OU=Groups,OU=Test,DC=Test,DC=Testal | CN=User2. CN=User4 CN=test O'Lord. CN=etc... |
CodePudding user response：
pls refer:
=TEXTJOIN(CHAR(10),1,FILTERXML("<a><b><c>"&SUBSTITUTE(SUBSTITUTE(A1,";","</c></b><b><c>"),",","</c><c>")&"</c></b></a>","//c[1]"))
CodePudding user response：
If there were no FILTERXL, please refer:
=TEXTJOIN(CHAR(10),1,IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A1,SMALL(IF(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),3)="CN=",ROW(INDIRECT("1:"&LEN(A1)))),ROW($1:$30)),99),",",REPT(" ",30)),20)),""))