find a phrase in a text field and provide the date that is written in a new field

[Bennet]

Hello,

I believe this question was asked before but I couldn't find the right answer. So please excuse if that is a repeated request.

My problem:

I do have a textfield where a "flowing text" is provided. Within this text there might be at some point the expression "Required start" followed by a date "dd.mm.yyyy". If that is the case the date should be provided in a new field. If not it should use an expression like "unknown".

Maybe a if then else could help but I do not know if I can use something like contains within this formula. As well as how to get the date that is behind the expression into a new field.

Example:

ID	description
1	ZP: 1999/ Required start 19.04.2017 / more information / reqested support/ alpha / SP: xzz / ZP: something/ 22.09.2016 /[T]L[/T][PR]3[/PR][N]1234[/N][S]1[/S][TN]
2	Egle / SP: xyz / ZP: Dr. H.E. / ZP: 1234 / Required start 16.05.2017 / more information / reqested support [T]L[/T][PR]3[/PR][N]1234[/N][S]1[/S][TN]

Sometimes there are more dates mentioned this is why I need to look up "required start" at first.

Thanks for your support

Br Bennet

[mborriero · Accepted solution]

Hi @Bennet, I would use a REGEX function to get "required date XX.XX.XXXX" in a new field, then you can use a formula removing the text "required date " and give unknown value for the null().

[Bennet]

Hi @mborriero,

that REGEX solution works fine but I am not quite sure which formula to use for the deleting part. Could that be done with a if then else? I am not sure which expression to use here. I would try:

 IF Contains([required Start date1],"date" THEN xxxx ELSE "unknown" ENDIF - what can I write for xxx? Using somthing like "delete"? I am not really familiar with that. :/

thanks for your support that is great. Even If I am still finding out what your expression: "Required start [0-9]+\.[0-9]+\.[0-9]+" means :). I do understand the numbers but why does \ has a "." behind it? Is that needed for "any single character"? what does that mean? no matter what is written afterwards?

Br Bennet

[mborriero · Accepted solution]

Hi @Bennet, the symbol "\" before the "." it means when the char is a "." (dot)

For example, if I write \$ means when the char is $. You can use the "\" whenever you want to specify a specific char.

The reason why I add the char "\" is because the symbol "." is used in REGEX to identify any single character.

Yes, you can use an if statement. I have used an IF statement to check if the value is null. If it is null I give UNKNOWN, if not I remove the text "Required start " from the string.

I hope it helps.

[Bennet]

Hi,

that cool. Works fine and is juuuust a little bit different then mine. ;)

Also thank you for your explanation. That is really helpful for the future.

Always nice to see the fast and useful support here.

Have a nice day

Br Bennet