PowerPivot extract text after different characters

July 2, 2024

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.