Using Reg ex to pick the vendor name from file path

[akumar2609]

Hi All,

I need help in extracting the vendor name from file path column using regex. I am using a batch macro to pull and combine all the data from a tab name called - 'Charge Net'.  After that I also need to know the vendor name from a column called as 'File Name'. Every time vendor name will be changed but Vendor name will always falls after  Input\ and end before .xlsx

Path:- a)  H:\Alteryx\Alteryx\xxxxxx\Input\Allocations I071133 xxxx (abcde).xlsx|||Charge Net

           b)  H:\Alteryx\Alteryx\xxxxxx\Input\Allocations ABC C-XY-0000000000.xlsx|||Charge Net

           c)  H:\Alteryx\Alteryx\xxxxxx\Input\Allocations TSQ012354 Txxxxnxxa ABC xxxx 12548ion11.xlsx|||Charge Net etc.

Please help me in designing the regex expression for the same.

[binuacs · Accepted solution]

@akumar2609 one way of doing this

REGEX_Replace([Path], '.*\\(.*)\.xlsx.*', '$1')

[akumar2609]

Thanks for the help. It worked for me. Could you please help me in explaining what you did here:-

REGEX_Replace([Path], '.*\\(.*)\.xlsx.*', '$1')

[binuacs]

@akumar2609  the requirement was to extract only the file from the given path, and it is starting after the last '\' and ending before the .xlsx, so we need only the text between last \ and .xlsx, 

so the regex formula reads till the \ which is part .*\\  - 

(.*) - any text after \ which is captured in a group and assign to $1

\.xlsx.* - any text after .xlsx

Part Meaning

.*	Match any character (.) zero or more times (*) — greedy, so it consumes as much as possible.
\\	Matches a literal backslash (\) — used as folder separator in Windows. The backslash is escaped as \\.
(.*)	Capturing group 1 — matches any characters (greedy) after the last backslash. This is the file name part.
\.xlsx	Matches the literal .xlsx extension (the dot . is escaped as \.).
.*	Matches anything after .xlsx (e.g., _backup, .tmp, etc.).

[akumar2609]

Thank you. It was really helpful.