Extracting Text from a text string

[tonyenglish]

Apologies if this has been asked before, but as a newbie to all things Power BI this is proving a bit of a pain.

The data I'm working with needs a certain bit of tidying before it's used.  One of the the things that is proving difficult is extracting a certain text string from another - it's easy enough to do in Excel/Access but I'm strugging with it here.

What I have is this....

Activity	CR
BAU SBCR811c-i Test	SBCR811c-i
R3 SBCR1022b Design	SBCR1022b
R3 SBCR1227 Requirements Gathering	SBCR1227
BAU SBCR811c-i	SBCR811c-i

and in Excel =IFERROR(LEFT(RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1), FIND(" ",RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1))),RIGHT(A2,LEN(A2)- FIND("SBCR",A2,1)+1)) does the trick

I've tried doing it as the data is imported but it gets messey pretty quickly.

I've also tried adding a measure using ....

IsCR = if(FIND("SBCR",[Activity],1,-1)>0, RIGHT([Activity],LEN([Activity])-FIND("SBCR",[Activity],1,-1)), "")

This gets me the first part but I'm struggling to knock off the text string at the end.

Unfortunately I can't ask that the data is aperated before I get it.. 

Regards,

Tony

[v-yuta-msft · Accepted solution]

Hi tonyenglish,

You want to extract substring with the beginning of “SBCR” from [Activity], right?

Click Query Editor->Transform->Split Column like belew.

Column [Activity.2] is what you want.

 

Regards,

Jimmy Tao