SOLVED

Get MULTIPLE values between characters

Contributor

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;FIND("]";B2)-1);FIND("[";B2)+1;LEN(B2))

 

But now I have cells with more than one word between [], for example:

 

[Invoiced Amount] + [Invoiced Freight Amount] + [Invoiced Packing Amount] + [Invoiced Other Amount]

 

 

I tried thinking about splitting the columns, but there is too much inconsistency. Here is another example to show the inconsistency 

 

VAR _NumOfWorkingDayswithRevenue = [Number of Working Days with Revenue]
VAR _NumofWorkingdaysinMonth = [Working Days in Month]
VAR Result =
CALCULATE(
    [Invoiced Net Revenue],
    SAMEPERIODLASTYEAR('Date'[Date])
) / _NumofWorkingdaysinMonth * _NumOfWorkingDayswithRevenue
RETURN
Result

 

 

I hope a creative mind will help me out!!

9 Replies

@Celia9  oooh i finally get to use this trick

try this:

=FILTERXML("<html>"&SUBSTITUTE(SUBSTITUTE(A1,"[","<p>"),"]","</p>")&"</html>","//p")

this will return all the values inside [] in new rows.  If you want then in columns then TRANSPOSE() if you want them together in the same cell use TEXTJOIN()

You are amazing!!!

Can I transpose them in the formula itself? Because now I get in trouble when there are multiple in a row.
Oh just use your formula in =TRANSPOSE
lol

Thank you, you are a lifesaver!

Hope you can help me out once more.. I get some errors, but I cannot see why.. Do you?

"VAR _MaxDate = MIN('Date'[Date])
VAR Result =
CALCULATE(
[Open PO Qty by Promise Date],
FILTER(
ALL('Date'[Date]),
'Date'[Date] < _MaxDate
)
)
RETURN
Result"


And another example:

"VAR Result = [Budgeted Revenue] - [Invoiced Net Revenue]
RETURN
IF(Result < 0, 0, Result)"


Both of them give: #VALUE!

20 out of 180 give this error.

@Celia9 It is the "<" symbol.  Try this:

=TRANSPOSE(SUBSTITUTE(SUBSTITUTE(FILTERXML("<html>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"<","#*LT*#"),">","#*GT*#"),"[","<p>"),"]","</p>")&"</html>","//p"),"#*GT*#",">"),"#*LT*#","<"))
best response confirmed by Celia9 (Contributor)
Solution

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

@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

image.png

Thanks all! I have it!!

@Celia9 

This uses functions that are in beta release.

= LET(
      tail, TEXTAFTER( data,"[",{1;2;3;4;5},,,""),
      head, TEXTBEFORE(tail,"]",,,,""),
      head
  )