String Manipulation within an Excel cell to build a list of substrings which match criteria-CodePudding

I would normally have coded my output prior if I had direct access to the system. Unfortunately, my current customer how shall I say, slightly antiquated. I have been given a csvde dump from active directory. Within Excel how I can extract all the 'CN=*,' values in cell A1 and list them into the adjacent cell A2?

A1	A2
CN=Organization Management,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=Discovery Management,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=VPN Users,OU=Groups,OU=TEST,DC=test,DC=local;CN=Debugger Users,CN=Users,DC=test,DC=local;CN=Wireless Users,OU=Groups,OU=TEST,DC=test,DC=local;CN=Users,DC=test,DC=local;CN=Exchange Public Folder Administrators,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=Exchange Organization Administrators,OU=Microsoft Exchange Security Groups,DC=test,DC=local

CN=Organization Management,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=Discovery Management,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=VPN Users,OU=Groups,OU=TEST,DC=test,DC=local;CN=Debugger Users,CN=Users,DC=test,DC=local;CN=Wireless Users,OU=Groups,OU=TEST,DC=test,DC=local;CN=Users,DC=test,DC=local;CN=Exchange Public Folder Administrators,OU=Microsoft Exchange Security Groups,DC=test,DC=local;CN=Exchange Organization Administrators,OU=Microsoft Exchange Security Groups,DC=test,DC=local

CodePudding user response：

With Excel 365 for Mac, you can do:

=LET( txt, A1,
        s, SEQUENCE(LEN(txt)),
        hdrSeq, (MID(txt,s,3)="CN=")*s, tlrSeq, (MID(txt,s,1)=",")*s,
        header, FILTER( hdrSeq, hdrSeq > 0 ), trailer, FILTER( tlrSeq, tlrSeq > 0 ),
        nxtTrlr, INDEX(trailer, IFERROR( XMATCH(header,trailer,1,-1), 1 ) ),
        MID( txt, header, nxtTrlr-header) )

where A1 contains the target text that you want to parse.

If you need it to spill right, this will do it:

=LET( txt, A1,
        s, SEQUENCE(LEN(txt)),
        hdrSeq, (MID(txt,s,3)="CN=")*s, tlrSeq, (MID(txt,s,1)=",")*s,
        header, FILTER( hdrSeq, hdrSeq > 0 ), trailer, FILTER( tlrSeq, tlrSeq > 0 ),
        nxtTrlr, INDEX(trailer, IFERROR( XMATCH(header,trailer,1,-1), 1 ) ),
        TRANSPOSE( MID( txt, header, nxtTrlr-header) ) )

Adding TEXTJOIN

Note JvdV's answer within the comments - super sleek and non-intuitive.

If you need this as a single cell text, then:

=LET( txt, A1,
    s, SEQUENCE(LEN(txt)),
    hdrSeq, (MID(txt,s,3)="CN=")*s, tlrSeq, (MID(txt,s,1)=",")*s,
    header, FILTER( hdrSeq, hdrSeq > 0 ), trailer, FILTER( tlrSeq, tlrSeq > 0 ),
    nxtTrlr, INDEX(trailer, IFERROR( XMATCH(header,trailer,1,-1), 1 ) ),
    TEXTJOIN(",", 1, MID( txt, header, nxtTrlr-header) ) )

CodePudding user response：

You may find a two-step solution useful:

use TextToColumns comand in Data tab to split string by semicolon, comma and equal signs:

use formula:

=TEXTJOIN(CHAR(10),1,"CN="&FILTER(B1:ZZ1,A1:ZY1="cn"))