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;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!!

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

9 Replies

PeterBartholomew1

Silver Contributor

Jul 03, 2022

Celia9

This uses functions that are in beta release.

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

Celia9
Brass Contributor
Jul 03, 2022
Thanks all! I have it!!

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

extract terms from the text.xlsx15 KB

Patrick2788

Silver Contributor

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*#","<"))
- Celia9
  Brass Contributor
  Jul 03, 2022
  You are amazing!!!
  
  Can I transpose them in the formula itself? Because now I get in trouble when there are multiple in a row.
  - Celia9
    Brass Contributor
    Jul 03, 2022
    Oh just use your formula in =TRANSPOSE
    lol
    
    Thank you, you are a lifesaver!

Forum Discussion

Get MULTIPLE values between characters

9 Replies

Resources