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

Question

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

use FILTERXML if you have it: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml — Scott Craner, Nov 11 '21 at 18:40
Unfortunately, FILTERXML is not available in Excel on the Mac, or in Excel Online. Which is another problem in its self — , Nov 11 '21 at 18:49
@JvdV - tmk, no. PQ is not available to Mac either. frustrating... — mark fitzpatrick, Nov 11 '21 at 19:43

mark fitzpatrick · Accepted Answer · 2021-11-11T21:49:07.667

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) ) )

accidentally left C2 inside of the formula instead of referencing it via **txt**. that's fixed now. If you need it to spill columnwise, wrap the last line **MID** in a TRANSPOSE. — mark fitzpatrick, Nov 11 '21 at 20:50
Well done! My two cents (not worth another answer) `=LET(X,LEN(A1),Y,SUBSTITUTE(SUBSTITUTE(A1,";",","),",",REPT(" ",X)),Z,TRIM(MID(Y,SEQUENCE(LEN(Y),,,X),X)),FILTER(Z,LEFT(Z,2)="CN"))`, which I think need to be still wrapped in a `TEXTJOIN()` for OP's needs. — JvdV, Nov 11 '21 at 21:35
Holy crap, @JvdV - that is a mind twist! Excellent! I did not note the TEXTJOIN need - perhaps I should add it, but it is pretty straight forward. — mark fitzpatrick, Nov 11 '21 at 21:45

score 1 · Answer 2 · answered Nov 11 '21 at 19:22

1

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"))

answered Nov 11 '21 at 19:22

basic

11,673
2
9
26

I need to do this for 200 cells within a single column. if there an alternative single step? – Nov 11 '21 at 21:08

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

2 Answers2

Adding TEXTJOIN