Forum Discussion
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...
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 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()
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.
- 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!
