 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;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 * _NumOfWorkingDayswithRevenueRETURNResult

I hope a creative mind will help me out!!

9 Replies

# Re: Get MULTIPLE values between characters

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

# Re: Get MULTIPLE values between characters

You are amazing!!!

Can I transpose them in the formula itself? Because now I get in trouble when there are multiple in a row.

# Re: Get MULTIPLE values between characters

Oh just use your formula in =TRANSPOSE
lol

Thank you, you are a lifesaver!

# Re: Get MULTIPLE values between characters

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.

# Re: Get MULTIPLE values between characters

@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

# Re: Get MULTIPLE values between characters

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

# Re: Get MULTIPLE values between characters

As variant

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

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 # Re: Get MULTIPLE values between characters

Thanks all! I have it!!

# Re: Get MULTIPLE values between characters

This uses functions that are in beta release.

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