Condition for extracting string from filename

[Mohd-Siddiqui1]

Hi there,

I have some files named as below:

input :

1. ABC_123r_EfsrTerhvhb

2. ABC_123456y_OUTERRG

3. ABC_ef567_itreqwrtohg

The "ABC_" is fixed in the starting name of the file. I need to fetch string (numeric value or alphabets) which is between first occurrence of underscore and second occurrence of underscore. The string can contain numeric values or alphabets.

After extracting the desired string from the filename, I need to check whether it is of length = 4 and it should have only numeric values.

[DataNath]

I believe this should do the trick - pulls out everything between the two underscores and then checks whether it's 4 numbers.

Added an extra input where this is the case to show it working.

[Luke_C]

Hi @Mohd-Siddiqui1 

Here's two ways, one with Regex and one with string functions.

1. Regex: ABC_(.*)_.* 
   1. The bolded piece is what we're parsing
   2. This assumes there will only be two underscores
2. String Function:
   1. LEFT(Replace([Filename],'ABC_',''),FindString(Replace([Filename],'ABC_',''), '_'))
   2. Replaces the standard prefix and returns everything to the left of the first underscore (after the prefix underscore)
3. Check:
   1. Filter for Length = 4 and IsInteger (checks to see if value can be converted to an integer successfully)
   2. The T anchor is records that pass, F is records that fail

[Mohd-Siddiqui1]

Apologies I have made some mistake while writing the filenames. The filenames is as below:

input :

1. ABC_123r_EfsrT_erhvhb

2. ABC_123456y_OUTE_RRG

3. ABC_ef567_itre_qwrtohg

[Mohd-Siddiqui1]

I want below output:

1. 123r

2. 123456y

3. ef567

then need to check length = 4 and all digits are numeric values.

[DataNath]

@Mohd-Siddiqui1 have amended the solution to be specific to the 1st/2nd underscores.