Extracting the Name in the Full Path

[KamenRider]

Hi,

I have the full path output as field name. For example [path name... |||'Marimar$'. I would like to get only the name Marimar or Jeger which is found at the end of the path which corresponds to sheet name.

Please let me lend any of your assistance.

Thanks,

Mar

[KamenRider]

Hi @binuacs @DylanDowrick @atcodedog05 

I made locating the shared folder to my home drive and the regex is still not working. I have checked the select tool and it is in VString with size of 1073741823.

In line to FileGetExt(), how it is being used? I use FileGetDir.

Thanks,

Kamen

[binuacs]

@KamenRider can you share your home drive path also the screen shot of your formula tool, where you are calling the regex formula?

[KamenRider]

Hi @binuacs ,

The data is confidential and I am very sorry. But I can change some part to illustrate or show how long it is as show below:

Column name: RAWRECON

\\NASOUT.AD.ABC COMPANY.COM\amerBBBS$\xbust\PAVHOME65\485636\PBMdesk\desktop\Roldan Files\Business Pattern\13_Alteryx\BAP-Payoff Detail\Output\Output + [filename] + ".xlsx|||" + [filename]

Then formula for regrex

REGEX_Replace([RAWRECON], ".*\|\|\|'(.+)\$'", "$1")

results:

\\NASOUT.AD.ABC COMPANY.COM\amerBBBS$\xbust\PAVHOME65\485636\PBMdesk\desktop\Roldan Files\Business Pattern\13_Alteryx\BAP-Payoff Detail\Output\Output\Recon Tracker 01/31/2022.xlsx|||Recon Tracker 01/31/2022

This means that the result did not shorten and the name only.

Thanks,

Kamen

[binuacs]

@KamenRider 

REGEX_Replace([RAWRECON], ".*\|\|\|(.+)", "$1")

[binuacs · Accepted solution]

@KamenRider another option using formula

substring([RAWRECON], FindString([RAWRECON], '|')+3,Length([RAWRECON]))

[DylanDowrick]

This screenshot is how FileGetExt() works. Then I used 'replacefirst([Tab Name],".xlsx|||","")' to finish off the cleanup.

In addition here is a formula that should produce the tab name regardless of length of filepath:

left(ReverseString(left(ReverseString([RAWRECON]),STRCSPN(ReverseString([RAWRECON]),"|"))),STRCSPN(ReverseString(left(ReverseString([RAWRECON]),STRCSPN(ReverseString([RAWRECON]),"|"))),"$"))

[KamenRider]

Hi @binuacs 

Finally your last formula using substring works. I can't understand but maybe you can tell me why your previous formula like regex is not working. Thanks a lot for your patience in helping me out to come up with the correct formula.

substring([RAWRECON], FindString([RAWRECON], '|')+3,Length([RAWRECON]))

Thanks,

Kamen

[atcodedog05]

Hi @KamenRider 

Awesome to hear it got solved 🙂👍

[binuacs]

@KamenRider  the Regex forumla REGEX_Replace([RAWRECON], ".*\|\|\|(.+)", "$1") first looking for the combination of the characters ||| and what ever comes after these characters is assigned to a group (.+) . Can you check your path again with this logic and try to analyze why it is not working.