Extract data from a column if the string matches multiple criterion

[rb160488]

Hello,

I need help in extracting data from 'Data Column' as shown below. The string match condition that I'm looking for are "FM-" as shown in ID#1, "FM x" (FM with a space followed by a number x) as shown in ID#9 & "FMx" where x is a number as shown in ID#6. Any other FM's not meeting the above 3 criterias should be ignored. Is it possible to extract the sample data in another column?

ID	Data Column	Output
1	Hello from FM-223, Random FM FM FM	FM-223
2	Sample Data XXXXXX	Null
3	Sample Data XXXXXX	Null
4	Sample Data XXXXXX	Null
5	Sample Data XXXXXX	Null
6	Hi from FM212…..XXXXX sample FM to be ignored	FM212
7	Lorem Ipsum	Null
8	placeholder text	Null
9	Bye FM 444 FM488 yes, more FMto be ignored	FM 444 FM488

[rsbin]

@rb160488 ,

Personally, I would spend some time to try to cleanse the data at source to make it easier to work with.

A bit of a challenge but I believe I have a solution for you.  Needed to use a combination of the various TEXT functions available in DAX.

My first step was to find the start digit if your [Data Column] contains "FM":

FIND FM = FIND( "FM",FM[Data Column ], 1, 0 )

ID	Data Column	Output	FIND FM
1	Hello from FM-223, Random FM FM FM	FM-223	12
2	Sample Data XXXXXX	Null	0
3	Sample Data XXXXXX	Null	0
4	Sample Data XXXXXX	Null	0
5	Sample Data XXXXXX	Null	0
6	Hi from FM212…..XXXXX sample FM to be ignored	FM212	9
7	Lorem Ipsum	Null	0
8	placeholder text	Null	0
9	Bye FM 444 FM488 yes, more FMto be ignored	FM 444	5

Next, I used the SWITCH Statement to create your 3 conditions given above:

Output1 = SWITCH( 
              TRUE(), 
              CONTAINSSTRING( [Data Column ], "FM-" ), 
                               MID( [Data Column ], [FIND FM], 6 ), 
                               // Satisfies the FM- scenario, but assumes only 3 digits after.
               CONTAINSSTRING( [Data Column ], "FM" ) && 
                 MID( [Data Column ], [FIND FM]+2,1) IN {"1", "2", "3","4","5","6","7","8","9"}, MID([Data Column ], [FIND FM], 5 ), 
                              // Satisfies "FM" & assumes 3 digits after 
               CONTAINSSTRING( [Data Column ], "FM ") && 
                 MID( [Data Column ], [FIND FM]+3,1) IN {"1", "2", "3","4","5","6","7","8","9"}, MID([Data Column ], [FIND FM], 6 ))

Output1

FM-223
FM212
FM 444

The only condition I wasn’t able to satisfy is if there are multiple FM’s in the same record.  I would need to give this one some extra thought.  There are probably alternative ways to play with the various text functions, but given all of the scenarios you provided, this was my best shot.  Hope you can make any adjustments necessary with your real world data.

https://learn.microsoft.com/en-us/dax/containsstring-function-dax

Regards,

[rb160488]

Thank you! Let me know if are able to figure out the Multiple FM's case.