563663_en-US

liliconcom

Substring of a file name column between uknown number of characters

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.

 

 

Advanced Statistical ModelingData Blending and CleanupMass CustomizationQuality and Process EngineeringRe: Substring of a file name column between uknown number of characters

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

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

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

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: