Forum Discussion

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

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

mtarler

Silver Contributor

Jul 03, 2022

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

Celia9

Brass Contributor

Jul 03, 2022

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.

mtarler

Silver Contributor

Jul 03, 2022

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*#","<"))