File name update with inconsistent number of characters

[kcoleman]

Hi all,

I'm trying to rename a group of files with a new generation date and date of report [beginning and end]

I was going off the basis that the middle section [between the third and sixth underscores _ ] always has the same number of characters, but as per below example that's not always the case. 

Is there any way to approach this variable? See below my currently formulae that extracted the "File Name Middle" section and then the later formulae that put my file name all back together

Substring([FileName],110,15)

"**Redacted File Location**\"+[File name start]+[Generation Date]+[File Name Middle]+[Report Date Begin New]+"_"+[Report Date End New]+[File Name End]

Thank you

[afv2688]

Hello @kcoleman ,

you can use the Padleft function to add whatever character you would like to assure that way always having the same length. In this case for example you could add:

Padleft([File Name Middle], 5, '0')

To have always 5 number combinations and if the number is lower add 0 at start, turning "_8721_" to "_08721_"

Regards

[kcoleman]

Thanks afv, I don't think that has worked for me unfortunately.

Here is my middle section, the first set of digits [#8271] could have anything between 2 and 5 digits depending on the report, the second set [#3] could have as many as 2.

[mceleavey]

Hi @kcoleman ,

Can you post the mock data showing what you have and what you're trying to achieve? An image is no good as we can't use it.

M.

[mceleavey]

@kcoleman ,

if you're just looking to pad out the section to contain the same amount of characters then I've attached a workflow that will be dynamic no matter how many characters.

This simply splits out the string by underscores, amend to the correct length, pivots into a column then concatenates back using an underscore separator:

This gives the following results:

Hope this helps,

M.

[danilang]

Hi @kcoleman 

Another way to go about it is to use Regex to extract every thing from the 3rd to the 6th underscore.  The formula is this

Regex_replace([Filename],"(.*?_){3}(\d+_\d+_\d+).*","$2") 

Broken down, the regex is 

(.*?_){3}  any number of characters followed by "_" repeated 3 times.   this is first marked group $1

(\d+_\d+_\d+)   where \d+ is any number of digits. this is the 2nd marked group $2

.* the rest of the string

Dan