Extract Text from a string from each row in a column

[SantoshKumar]

Hi All,

I have a Column which contains data as below:

Tags	Expected_New_Column
Compulsion Games; GACCYellow; Gaming Accessibility;  MAS2.4.7; TP3; Accessibility;	MAS2.4.7
Accessibility;  GACCYellow; Gaming Accessibility;  MAS2.4.3; TP3; Compulsion Games;	MAS2.4.3
Accessibility;  GACCLime; Gaming Accessibility;  MAS4.1.2; TP3;  Compulsion Games;	MAS4.1.2
Accessibility; Compulsion Games; GACCLime; Gaming Accessibility;  MAS4.1.2; TP3;	MAS4.1.2
Accessibility;   MAS1.4.10; TP3;  Compulsion Games; GACCYellow; Gaming Accessibility;	MAS1.4.10
Compulsion Games; GACCLime; Gaming Accessibility;  MAS2.4.3; TP3; Accessibility;	MAS2.4.3
Accessibility;  Compulsion Games; GACCYellow; Gaming Accessibility;  MAS2.4.3; TP3;	MAS2.4.3
Accessibility;  GACCLime; Gaming Accessibility; MAS4.1.2; TP3;	MAS4.1.2

I was not able to use Delimeter or Trim, LEFT, RIGHT,SUBSTITUTE etc to get my expected output.

Can anyone provide any DAX query to create new column as shown above. 

thanks

Santosh

[harshnathani · Accepted solution]

Hi @SantoshKumar ,

Create a Calculated Column

Column = 
var a = SEARCH("MAS",'Table'[Tags],,999)
var b = SEARCH(";",'Table'[Tags],a,999)
var c = MID('Table'[Tags],a,b-a)

RETURN
c

Regards,

Harsh Nathani

Appreciate with a Kudos!! (Click the Thumbs Up Button)

Did I answer your question? Mark my post as a solution!

[ryan_mayu]

@SantoshKumar 

please try this. I found the common of the part that you want is starting from MAS and before "TP" . It this is just an coincidence. Then this solution does not work.

Column = 
VAR a=find("MAS",'table'[Tags],1)
VAR b=find("TP",'table'[Tags],a)
return mid('table'[Tags],a,b-a-2)

[SantoshKumar]

@ryan_mayu 
Thanks for your response but TP will not be always available after MAS for this case. i got the solution with the use of Search and MID option.

[Greg_Deckler]

@SantoshKumar - Sure, use SEARCH or FIND to get the position of MAS. Use that starting position to then SEARCH or FIND for the next semi-colon. Use MID to get the text between those two positions.