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 )
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 |
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 )Thank you so much for the quick and helpful solution!
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?
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 )Thank you! That solved all my issues, very much appreciated!