Calculate a new column based on part of source file name

1

Hi,

Currently I have a process going, where the source is from excel files with consistent naming format [Project Number - Project Name - Location - Team - Month.xlsx]. However ‘Team’ name varies in length and someone contains dash, sometimes not.

Each excel files contains a list of candidate names.

Sample file names:
P01 - Recruitment - London - Team Red - A1 - July.xlsx
P01 - Recruitment - London - Team Red - B1 - July.xlsx
P02 - Promotion - Paris - Team Blue - A12 - August.xlsx
P02 - Promotion - Paris - Team Blue - B1 - August.xlsx
P03 - Retention - New York - Team Orange - June.xlsx

I would like to create a new column that contains team name (as per below example)
image

Just wondering if there is a way for me to do this?
Thanks in advance.

2

If the team name always starts from “Team” and the month name is always separated by a dash you can use functions keepafter() and keepbefore() to extract the team name. See the example below:

text-parse.morph (3.5 KB)

image
image804×441 12.4 KB

3

Thank you dgudkov for the solution!