Forum Discussion

OSSKD1's avatar
OSSKD1
New Member
3 years ago
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...
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi OSSKD1 
    Please refer to attched file with the solution

    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], "-" )
RETURN
    IF ( CONTAINSSTRING ( Result, "-" ), Result )
  • tamerj1's avatar
    tamerj1
    3 years ago

    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 )
daXtreme's avatar
daXtreme
Solution Sage
3 years ago

You use Power Query for such data mangling...