Get text between specific characters

[Anonymous]

Hi,

I'm trying to get name in a long string. 

The string is like

"operation = 'accept', name = 'Lucy'; operation = 'cancel', name = 'Tina'; operation = 'end', name = 'Tim'"

I only want the name of person who does the cancel operation which is Tina.

Can anybody help? How can I achieve this by adding a calculated column?

[az38 · Accepted solution]

Hi @Anonymous 

try a DAX calculated column

Column = 
var _start = SEARCH("cancel", 'Table'[LongStringColumn]) + 17
var _end = SEARCH("operation", 'Table'[LongStringColumn], _start) - 3
RETURN
MID('Table'[LongStringColumn], _start, _end - _start)

[ryan_mayu · Accepted solution]

@Anonymous 

Column = 
VAR a = SUBSTITUTE(SUBSTITUTE('Table'[column1],"'","/",7),"'","/",7)
VAR loca=find("/",a,1,0)
VAR locb =find("/",a,loca+1,0)
return mid(a,loca+1,locb-loca-1)

[mahoneypat · Accepted solution]

In the query editor, add a Custom Column with this formula 

Text.BetweenDelimiters([ColumnWithText], "'cancel', name = '", "'")

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[ryan_mayu · Accepted solution]

@Anonymous 

Column = 
VAR a = SUBSTITUTE(SUBSTITUTE('Table'[column1],"'","/",7),"'","/",7)
VAR loca=find("/",a,1,0)
VAR locb =find("/",a,loca+1,0)
return mid(a,loca+1,locb-loca-1)

[az38 · Accepted solution]

Hi @Anonymous 

try a DAX calculated column

Column = 
var _start = SEARCH("cancel", 'Table'[LongStringColumn]) + 17
var _end = SEARCH("operation", 'Table'[LongStringColumn], _start) - 3
RETURN
MID('Table'[LongStringColumn], _start, _end - _start)

[Ashish_Mathur]

Hi,

Try using the "Column from Examples" featue in the Query Editor.