Substring of a file name column between uknown number of characters

[#1]

When i input data I end up with a file name column that looks like this:

File Name

Test Name Date ID# Wafer and Device ID1 #ID comment

Test Name Date ID# Wafer and Device ID2 #ID different comment

Different Test Name Date ID# Wafer and Device ID1 #ID comment

Different Test Name Date ID# Wafer and Device ID2 #ID different comment

What I want is the following:

File Name

Wafer and Device ID1

Wafer and Device ID2

Wafer and Device ID1

Wafer and Device ID2

All of these have different delimiters and string lengths, what I want to do is extract the Wafer and Device ID from with the 'ID# ' and ' #ID' characters minus the spaces. I'm finding a lot of information on taking out a specific number of characters, but I am struggling to find any information on searching a string and removing a string (in this case 'ID# ' and everything before) from the left and a string (in this case ' #ID ' and everything after) from the right and leaving the string in the middle.

Thank you in advance for your assistance.

[#2]

The Word() function can do this easily.

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:File Name = Word( 1, :File Name, "#" )
);

[#3]

Jim,

I did try this method already and again just now. It ends up removing everything after the first '#' character. So now I'm getting this for each row:

Test Name Date ID

It looks like it may be possible using munger or substr but I'm not getting that to work either.

[#4 · Accepted solution]

Oops....just change to extract the second word

Names Default To Here( 1 );
dt = Current Data Table();
For Each Row(
	:File Name = Word( 2, :File Name, "#" )
);

Please take the time to read about the Word() function in the Scripting Index.  It will show what each element can be, and an example of how it works.

[#5]

I love using the Word() function when the extraction is simple. Here is another approach using the Regex() function. The same approach can be used if the extraction pattern is not simple, just by changing the regular expression.

Names Default to Here( 1 );

// set up example
dt = New Table( "Untitled",
	Add Rows( 4 ),
	New Column( "File Name",
		Character,
		"Nominal",
		Set Values(
			{"Test Name Date ID# Wafer and Device ID1 #ID comment",
			"Test Name Date ID# Wafer and Device ID2 #ID different comment",
			"Different Test Name Date ID# Wafer and Device ID1 #ID comment",
			"Different Test Name Date ID# Wafer and Device ID2 #ID different comment"
			}
		)
	)
);

// make a data column for result
dt << New Column( "Extract", "Character", "Nominal" );

// compute row-wise result
For Each Row(
	:Extract = Regex( :File Name, "ID# (.+) #ID", "\1" );
);

Here is the result: