Forum Discussion
Solved
Extract specific text from string
I need to extract the 6 digit number, dash, and either 1,2, or 3 digit number following the dash from the dataset shown below. What is the easiest way to accomplish this as there isn't consistency in...
Hi OSSKD1
Please refer to attched file with the solutionResult = VAR String = 'Table'[Text] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) ) VAR Result = CONCATENATEX ( T4, [@Item], "-" ) RETURN IF ( CONTAINSSTRING ( Result, "-" ), Result )Hi OSSKD1
Attached file with small correctionResult = VAR String = 'Table'[Text] VAR Items = SUBSTITUTE ( String, " ", "|" ) VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 ) VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" ) VAR T2 = GENERATESERIES ( 1, Length, 1 ) VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) ) VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) ) VAR Result = CONCATENATEX ( T4, [@Item], "-", [Value], ASC ) RETURN IF ( CONTAINSSTRING ( Result, "-" ), Result )
Hi OSSKD1
Attached file with small correction
Result =
VAR String = 'Table'[Text]
VAR Items = SUBSTITUTE ( String, " ", "|" )
VAR Length = COALESCE ( PATHLENGTH ( Items ), 1 )
VAR T1 = SELECTCOLUMNS ( GENERATESERIES ( 0, 9, 1 ), "@Digit", [Value] & "" )
VAR T2 = GENERATESERIES ( 1, Length, 1 )
VAR T3 = ADDCOLUMNS ( T2, "@Item", PATHITEM ( Items, [Value] ) )
VAR T4 = FILTER ( T3, NOT ISEMPTY ( FILTER ( T1, CONTAINSSTRING ( [@Item], [@Digit] ) ) ) )
VAR Result = CONCATENATEX ( T4, [@Item], "-", [Value], ASC )
RETURN
IF ( CONTAINSSTRING ( Result, "-" ), Result )
Thank you! That solved all my issues, very much appreciated!