Extract data from a column if the string matches multiple criterion

[excelvbanoob420]

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?

Thanks!

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

 

[Rick Rothstein]

I see you wanted both qualifying FM values in ID#9 retrieved, in different cells in the same column. That was an easy example for you to show us as ID#9 was the last ID in the column, but what if such text happened in ID#6 instead? Should the output go in the Output column for the rows opposite ID#6 and ID#7? If so, where would the output go if such text was in both ID#6 and ID#7? Even more confusing, what if ID#6 and ID#9 had text with 2 qualifying FM values and ID#7 had text with 4 qualifying FM values so that ID#6 would overlap ID#7 and ID#7's 4 values would overlap ID#9's location? Basically, I do not understand your rules for outputting the qualifying FM values.

 

[excelvbanoob420]

I see you wanted both qualifying FM values in ID#9 retrieved, in different cells in the same column. That was an easy example for you to show us as ID#9 was the last ID in the column, but what if such text happened in ID#6 instead? Should the output go in the Output column for the rows opposite ID#6 and ID#7? If so, where would the output go if such text was in both ID#6 and ID#7? Even more confusing, what if ID#6 and ID#9 had text with 2 qualifying FM values and ID#7 had text with 4 qualifying FM values so that ID#6 would overlap ID#7 and ID#7's 4 values would overlap ID#9's location? Basically, I do not understand your rules for outputting the qualifying FM values.

Thank you for your reply and apologies for the confusion. In case of 2 qualifying FM values, output should be in the same (I fixed it). Secondly, I added a pre-condition column, which should be checked for "Valid" values before outputting the FM values.
Thanks in advance for the help.

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

 

[excelvbanoob420]

Hello, can someone help please?