File name update with inconsistent number of characters

Question

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

danilang · Answer

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

RegexMiddlePart.yxmd

mceleavey · Answer

@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.

mceleavey_solution.yxmd

mceleavey · Answer

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.