How to remove and replace everything after backslash

[apaicanada]

Hi,

I am trying to create an application in designer which allows users to scan a folder for any excel files that have a specific sheet, however I am having a bit of trouble using the update value with formula. 

Wondering how I would create a formula in the action tool that would take an input of: 

C:\Users\A172487\Downloads\Standard Templates\Standard Timesheet Template 2 - NO ADP OUTPUT - Copy.xlsx|||`Timesheet Input$`

To:

C:\Users\A172487\Downloads\Standard Templates\*.xslx

My goal is to try and remove everything after the last backslash and replace it with "*.xslx".

Any help is appreciated - thank you!

[CharlieS · Accepted solution]

You can use this expression in a Formula tool to create the desired string:

Left([Input],Length([Input])-FindString(ReverseString([Input]),"\"))+"*.xlsx"

The idea is that we want the beginning (Left) of the string, and to find the last "\" character, reverse the string and FindString( will locate the "first" one. Once the original is cut down to what we need, we can add the +"*.xlsx" to the end.

[Thableaus]

Hi @apaicanada 

If you want replace dynamically with a regex expression, this could work:

REGEX_Replace([Field1], "(^.*\\)(.*)", "$1*.xlsx")

But I'm wondering if you really need this. I think there could be easier solutions to your problem. Do you mind sharing your app architecture with us?

Cheers,

[apaicanada]

Thank you all for your solutions so fast!

[HomesickSurfer]

Hi

If you wanted to go up another level in the directory path, this can work for you.

IF
EndsWith([Path], "\")
THEN
ReverseString(ReplaceFirst(ReverseString([Path]), "\", ""))
ELSE
[Path]
ENDIF

-----------------------------------------------------------------------------

Left([Path],Length([Path])-FindString(ReverseString([Path]),"\"))