Using Reg ex to pick the vendor name from file path

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.

Regex

Accepted answers

binu_acs

@akumar2609 one way of doing this

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

All comments

binu_acs

@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')

binu_acs

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703