Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello experts I need your help trimming a string
I have a list of strings like below
Alice Anto (QueensLake\AliceA)
Allen Wendy (QueensLake\AllenW)
Dilys Agnes (QueensLake\DilysA)
I need to trim from '('. I need just the first two words.
I also need to trim the following
2017-04-08 TO4-32:36:29.2Z
2018-04-07 TI8-32:36:29.2Z
I need my date to read YYYY-MM-DD HH:MM (like 2018-04-27 32:36).
Thanks for your suggestions
Hey !
Use the subfeild function -
Cheers
Adithya
Hi Ngumdo
Usign index() to find charector = ( and the left() to determin the string lenght from the left, should do the job.
Left( FIELD, Index( FIELD, '(' )-1 )
Br.
Teis
Try this
=Left(FieldName, Index(FieldName, ' ('))
For Date and time you can try this
=TimeStamp(Date#(SubField('2017-04-08 T04-32:36:29.2Z', ' T', 1), 'YYYY-MM-DD') + Time#(PurgeChar(SubField('2017-04-08 T04-32:36:29.2Z', '-', -1), 'Z'), 'hh:mm:ss.fff'), 'YYYY-MM-DD hh:mm')
But I am not sure how you have a time of 32 hours? Usually the day changes after 24 hours... is this duration instead of time?
Thanks for your suggestion. It is duration not time and I have hundreds of rows to fix those two are just examples of how the other rows of data look like. Is there a way I can fix all with just one script without having to refer to each row?
You don't have to refer to each value... all you need is to use the field name to fix the date.
For duration, you can try this
=Date#(SubField(FieldName, ' T', 1), 'YYYY-MM-DD')
& ' ' &
Interval(Interval#(PurgeChar(SubField(FieldName, '-', -1), 'Z'), 'hh:mm:ss.fff'), 'hh:mm')
Example
=Date#(SubField('2017-04-08 T04-32:36:29.2Z', ' T', 1), 'YYYY-MM-DD')
& ' ' &
Interval(Interval#(PurgeChar(SubField('2017-04-08 T04-32:36:29.2Z', '-', -1), 'Z'), 'hh:mm:ss.fff'), 'hh:mm')
The script below removed the 'T' but I still need to trim from from the second ':' to the end of the string so that it reads something like 2018-06-27 22:37. Any help will be appreciated
Looks okay to me