exceldaxoffice365powerpivot

PowerPivot extract text after different characters

I need to extract text after different delimiters.

I have used this which works for the ones which do have "-" however the data is not all uniform and some have these as delimiters as well: "--" & "–" what way can I include these in the below code:

=TRIM(IF([Product]="","", VAR String = [Product]
VAR Items =
SUBSTITUTE (String, "-", "|" )
RETURN
PATHITEM ( Items, 2 )))

I have tried using 'AND' but I don't believe I am doing it correct.

Thanks for any assistance in advance.

Solution 
  • =TRIM(
  IF([Product]="", 
    "", 
    VAR String = [Product]
    VAR Items = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(String, "–", "|"), "--", "|"), "-", "|")
    RETURN PATHITEM(Items, 2)
  )
)

    This way, no matter which of the delimiters is used, it will be replaced by "|", and PATHITEM will correctly extract the second part of the string.