Extract specific text from string

[OSSKD1]

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 · Accepted solution]

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]

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 · Accepted solution]

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]

Hi tamerj1,

Quick follow up from yesterday's solution. I noticed in my master data and also in the file you provided, that in certain rows, the sequence was reversed (the 3 digits are now in front and the 6 digits are last) per the screenshot below. Any ideas what in the DAX might cause this?

[tamerj1 · Accepted solution]

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 )

[OSSKD1]

Thank you! That solved all my issues, very much appreciated!

[OSSKD1]

Thank you so much for the quick and helpful solution!

[tamerj1]

Hi @OSSKD1 
Please copy/paste the same sample data

[daXtreme]

You use Power Query for such data mangling...