Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Substring

I want to find everything after the first colon and  everything before the last hyphen, please see sample data below and the output am expecting        ID Output BOA:ServiceJourney:979-349...
Need Help
amitchandak's avatar
amitchandak
Super User
6 years ago

All are dax functions, Replace A2 with the column name

Create a new column

=MID(MID(Table[ID],1+FIND("|",SUBSTITUTE(Table[ID],":","|",2)),100),1,FIND("|",SUBSTITUTE(MID(Table[ID],1+FIND("|",SUBSTITUTE(Table[ID],":","|",2)),100),"-","|",LEN(MID(Table[ID],1+FIND("|",SUBSTITUTE(Table[ID],":","|",2)),100))-LEN(SUBSTITUTE(MID(Table[ID],1+FIND("|",SUBSTITUTE(Table[ID],":","|",2)),100),"-",""))))-1)

 

https://docs.microsoft.com/en-us/dax/substitute-function-dax

https://docs.microsoft.com/en-us/dax/find-function-dax

https://docs.microsoft.com/en-us/dax/left-function-dax

https://docs.microsoft.com/en-us/dax/len-function-dax