Difficulties with RegEx

Hi all,

I'd like to parse out some folder names and use them to organize my data. I'm currently using a directory tool that outputs the filename and uses batch macros to combine multiple files together. The macro works, now I'm just trying to grab the folder that each file came from as an additional data field.

Currently the File name looks like this: \\SharedDrive\Organization\Folder Name 1\Folder Name 2\Survey.xlsx|||'Survey 2015$'

I know this can be done with multiple text to column tools using "\" as a delimiter, however, this task I do relatively frequently and would like to know how I can use RegEx to parse out that folder name. I have previously used [-](.*) regex to treat "-" as my delimiter, but I am unsure on how to use special characters like "\".

Any help is greatly appreciated!

Thank you,

Jesse

Regex

Parse

Accepted answers

MarqueeCrew

Try this:

Regex_replace(Path,".*\\(.*)\\$", '$1')

It might work.

Cheers,

Mark

All comments

Claje

Unfortunately I don't have time at this second to offer you a RegEx solution - I'm sure someone else can offer one.

Here's a solution to get the folder name using one formula:

Replace([FILENAME],RIGHT([FILENAME],FINDSTRING(REVERSESTRING([FILENAME]),'\')),'')

Replace the [FILENAME] references with the field you are using.

This will essentially look for anything right of the last "\" character (the name of the file itself), and then replace that with nothing, leaving you with the folder path only.

MarqueeCrew

@Jesse89,

Have you tried

FileGetDir([Path])

Here is some more "stuff" from help ...

FileAddPaths(Path1, Path2)

Adds two file path parts, making sure there is exactly one \ (backslash) between the two paths.

Example

FileExists(Path)

Returns true if the file exists, false if it does not.

Example

FileGetDir(Path)

Returns the directory portion of the path.

Example

FileGetDir(C:\Temp\Data\file.csv) returns "C:\Temp\Data"

FileGetDir(C:\Temp\Data) returns "C:\Temp"

FileGetExt(Path)

Returns the extension of the path, including the . (period).

Example

FileGetFileName(Path)

Returns the name portion of the path, without the extension.

Example

Cheers,

Mark

Claje

Wow, I can't believe I literally never looked at these functions before. That's a much more elegant solution.

Jesse89

Thanks Mark,

I was able to use FileGetDir([Path]) to narrow it down. For the example used, this would give me "\\SharedDrive\Organization\Folder1\".

I then used two additional steps:

Step 1: ReplaceFirst ([Path], "\\SharedDrive\Organization\", ""),

Output: "\Folder1\".

Step 2: Trim([Path], "\")

Output: "Folder 1".

It would be helpful if anyone had a RegEx solution, since the organization name could change in the future, which would render my solution ineffective.

Thank you!

Jesse

MarqueeCrew

Try this:

Regex_replace(Path,".*\\(.*)\\$", '$1')

It might work.

Cheers,

Mark

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280