Forum Discussion
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 | 979 |
| BOA:ServiceJourney:1-1515-1644 | 1-1515 |
| BOA:ServiceJourney:2071-351 | 2071 |
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
1 Reply
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