Forum Discussion

Celia9's avatar
Celia9
Brass Contributor
Jul 03, 2022
Solved

Get MULTIPLE values between characters

Hi!   I use a formula to get the value between [] For example:   SELECTEDVALUE(Division[SubDivision Name], "Multiple Divisions")   I use this formula, which works great: =MID(LEFT(B2;FI...
excel
Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Jul 03, 2022

    Celia9 

    A variant:

    =LET(b,B2,arr,MID(b,SEQUENCE(LEN(b)),1),position,SEQUENCE(ROWS(arr)),LBracket,FILTER(position,arr="[")+1,Rbracket,FILTER(position,arr="]"),string,MID(b,LBracket,Rbracket-LBracket),TRANSPOSE(string))
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jul 03, 2022

Celia9 

As variant

getTerm=
LAMBDA( str,
    LET(
        n, LEN(str), 
        head, RIGHT( str, n- FIND("[", str)),
        LEFT( head, FIND( "]", head ) -1 )
    ));

tail=
LAMBDA( str,
    LET(
        n, LEN(str),
        RIGHT( str, n- FIND("]", str))
    )
);

stackTerms=
LAMBDA( str,
    LET(
        a, getTerm(str),
        IF( ISERROR(a), "",
            VSTACK( a, stackTerms( tail(str)))) )
    );

listTerms= LAMBDA( str,
    IFERROR( DROP( stackTerms( str), -1),
        "nothing is found in that text"
));

which looks like

Resources