How to trim the value before and after specific characters

[Bansi08]

Hi there,

I have column where values are like below table. I want trim this value such a way before gp- and after _2023 characters will get eliminated and i will get below output data. these are just sample values i have many records with such scenarios but condition is same for all. Can anyone please help me on this. 

Barbados-capital-investments-gp-growth-plan-series-i_2023_06_30_fund-performance-report

Barbados-capital-investments-gp-growth-plan-series-ii_2023_06_30_fund-performance-report

Output data

growth-plan-series-i

growth-plan-series-ii

[Luke_C · Accepted solution]

Hi @Bansi08 

You could use a formula like this:

Regex_replace([Data],'.*gp-(.*)_2023.*','$1')

the regex expression can be broken down as:

.* > any number of characters

gp- > specifically 'gp-'

(.*) > any number of characters (the parenthesis indicate this is what we want)

_2023 > specifically '_2023'

.* > any number of characters

[FinnCharlton]

Hi @Bansi08 , I would probably use the Parse function of the RegEx tool to do this:

[Bansi08]

I used Luke's solution. Its working absolutely fine as expected. Thanks!

[Luke_C]

Hi @Bansi08 

Can you provide accurate sample data then?

[Bansi08]

why we used $1 in the formula, could you please advise. 

[Luke_C · Accepted solution]

Hi @Bansi08 

$1 indicates that we want to select the first "marked group" (indicated by being in parenthesis). So that's what tells us to keep the values you want. Regex expressions can have multiple marked groups, so $1 $2 $3 and so on for however many things you want to separate out

[Bansi08]

Hi Luke -- Would you please also help with expression to trim only date from given name. I want to populate 2023-06-30 date from given name using regex_replace function using formula tool. 

I tried but unable to write correct expression. 

Thank you

[Luke_C · Accepted solution]

Hi @Bansi08 

something like this should work:

Regex_replace([Data],'.*(\d{4}_\d{2}_\d{2}).*','$1')

then you would use a datetimeparse function to get it into a date format:

DateTimeParse([Data],'%Y_%m_%d')

[Bansi08]

Hi Luke -- Will you please share workflow using sample data. I am not able to implement given solution in my workflow. I have already resolved this hurdle but your solution seems better than mine to handle future issues. 

Thanks!