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 the original dataset.

 

 

  • 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 )

8 Replies

  • OSSKD1's avatar
    OSSKD1
    New Member
    3 years ago

    Copying data similar to screenshot supplied previously:

    FOR CATS RFQ REF 254066-912
    263117 - 3
    FOR CATS RFQ 247178-4
    FOR CATS RFQ REF 249237-9
    FOR CATS RFQ 255000-44
    54 V&V PLANS & TESTING
    FOR CATS RFQ 229218-71
    FOR CATS RFQ REF 247178-18
    FOR CATS RFQ 255840-187
    ENG FEES (CATS HRS) RFQ: 245774-9
    FOR CATS RFQ 246784-29
    FOR CATS RFQ 247178-7
    280352 - 1
    FOR CATS RFQ 240026-26
    FOR CATS RFQ 255000-42
    276196 - 16
    276084 - 78
    276922 - 6
    276196 - 139
    FOR CATS RFQ 242212-14
    276922 -  5
    FOR CATS RFQ REF 247095-4
    263051 - 86
    FOR CATS RFQ 247178-5
    264720 - 255
    FOR CATS RFQ 255278-8
    FOR CATS RFQ 254104-5
    FOR CATS RFQ 247178-15
    277337 - 21
    270392 - 25

     

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

        Thank you so much for the quick and helpful solution!