Custom column to extract part of the text

[Earl40]

I have  a file that has a name column that contains 2 type of text before the actual name. All items will have (AB/CF) infront (used X;s to represent the name) but some will also have USA - after. Is it possible to create a custom column and have a dax formula remove  the (AB/CF) from all the names but also remove USA - where applicable? Sample screenshot below

Thank You in advance

[khaledHabib90 · Accepted solution]

The easier solution would be to use power query

1- duplicate the column if you want to keep the original form of the data

2- perform 2 replace operations in power query , first replace the (AB/CF) with blank then USA-

Add spaces before and after as appropriate so that they are removed with the replace operations

If my reply helped you solve your problem please accept this as the solution 🙂

[Ahmedx]

pls try this

[Bibiano_Geraldo]

Try the following code:

CustomColumn = SUBSTITUTE(SUBSTITUTE([Your_Column_Name], "(AB/CF)", ""), "USA - ", "")

[v-tianyich-msft · Accepted solution]

Hi @Earl40 ,

If you want to achieve this with DAX, you can try

Measure = IF(CONTAINSSTRINGEXACT(MAX('Table'[Original Name]),"(AB/CF) USA-"),SUBSTITUTE(MAX('Table'[Original Name]),"(AB/CF) USA-",""),IF(CONTAINSSTRINGEXACT(MAX('Table'[Original Name]),"(AB/CF)"),SUBSTITUTE(MAX('Table'[Original Name]),"(AB/CF)",""),0))

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[khaledHabib90 · Accepted solution]

The easier solution would be to use power query

1- duplicate the column if you want to keep the original form of the data

2- perform 2 replace operations in power query , first replace the (AB/CF) with blank then USA-

Add spaces before and after as appropriate so that they are removed with the replace operations

If my reply helped you solve your problem please accept this as the solution 🙂