Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a certain data value of which I need to exclude a certain portion of it.
The data is as follows: 1 h 5 m 4 s 841 ms
I need to exclude the '841 ms' value from the string. What approach/functions can be used?
Thanks and regards,
Erwin
Something like this?
IF (right(StringField, 2) = 'ms', left(StringField, index(StringField, ' ', -2)-1), StringField)
May be like this
=Left('1 h 5 m 4 s 841 ms', Index('1 h 5 m 4 s 841 ms', ' ', -2)-1)
or this using a field
=Left(FieldName, Index(FieldName, ' ', -2)-1)
One way could be:
left([theField], index([theField], 's'))
That expression finds the position of the first letter 's' in theField, and then returns only the leftmost part of the string up until (and including) that 's'.
Of course in this approach there must be an 's' for seconds in your string, otherwise the function would find the 's' in 'ms' and you don't want that...
Hi Henrik, is there no way that I identify the keyword ms in the data and then exclude it. If the milli second value is not present, then there is no need for a substring.
Sure, use IF, somthing like this:
if(index([theField], 'ms')>0, 'ms exists', 'ms doesn't exist')
Replace my string values with expression of your choice.
Something like this?
IF (right(StringField, 2) = 'ms', left(StringField, index(StringField, ' ', -2)-1), StringField)
Thank you Peter, it works perfect.