Forum Discussion
Get text between specific characters
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?
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)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)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
4 Replies
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)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)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
Hi,
Try using the "Column from Examples" featue in the Query Editor.
