Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
OSSKD1
New Member

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.

 

OSSKD1_0-1667840673382.png

 

2 ACCEPTED SOLUTIONS

Hi @OSSKD1 
Please refer to attched file with the solution

1.png

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 )

View solution in original post

Hi @OSSKD1 
Attached file with small correction

1.png

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 )

View solution in original post

8 REPLIES 8
OSSKD1
New Member

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

1.png

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 )

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?

 

OSSKD1_0-1667915707140.png

 

Hi @OSSKD1 
Attached file with small correction

1.png

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!

Thank you so much for the quick and helpful solution!

tamerj1
Super User
Super User

Hi @OSSKD1 
Please copy/paste the same sample data

daXtreme
Solution Sage
Solution Sage

You use Power Query for such data mangling...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.