topic Re: Substring of a file name column between uknown number of characters in Discussions

Substring of a file name column between uknown number of characters

trevorphysics — Sat, 10 Jun 2023 23:56:29 GMT

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.

Re: Substring of a file name column between uknown number of characters

txnelson — Wed, 02 Nov 2022 21:02:44 GMT

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, "#" )
);

Re: Substring of a file name column between uknown number of characters

trevorphysics — Wed, 02 Nov 2022 21:10:38 GMT

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.

Re: Substring of a file name column between uknown number of characters

txnelson — Wed, 02 Nov 2022 21:28:02 GMT

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.

Re: Substring of a file name column between uknown number of characters

Mark_Bailey — Thu, 03 Nov 2022 13:43:48 GMT

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: