RegEx tool - part of field name

[helen_c]

Hi all,

I've stacked several Excel files using the Input tool (with a wildcard) I have 'filename' as a field and I'd like to pull out part of the filename and create a new field. Example:

Filename

Complete_University_Guide_Accounting_And_Finanace_2022

Complete_University_Guide_Nursing_2022

Complete_University_Guide_Sport_Science_2022

I'd like the subject pulling out as a field name:

Subject

Accounting and Finance

Nursing

Sport Science

Any help would be great - thanks!

[mceleavey · Accepted solution]

Hi @helen_c ,

I love Regex.

Possibly too much.

This was simply a case of removing the common text between the filenames, then replacing the underscores with spaces.

Workflow attached.

Hope this helps,

M.

[helen_c]

That's perfect - thank you.

[mceleavey]

[ChrisTX]

I like RegEx too.  Not sure if I'll ever love it.  It's definitely very powerful. But it can be a little intimidating, especially for beginners.

For simple text like this, a Replace function would also work.  Use 3 separate formulas like below, or combine them all into one.

Replace([Filename], "Complete_University_Guide_", "")

Replace([Filename], "_2022", "")

Replace([Filename], "_", " ")

RegEx would avoid the hard coding.

Chris

[helen_c]

Can I also use RegEx to move the year into another column?

Thanks

[helen_c]

Thanks for this. I definitely find RegEx intimidating!

[ChrisTX]

The RegEx tool in parse mode can help move the Year into a new column.

Regular Expression:              .*_(.*)