How to start get strings when end with a string?

ylai20 · Post by **ylai20** » Tue Jan 18, 2011 1:23 am

Hi, i have a column has this kind of text:
This house is belongs to Andy
This car is belongs to Kelvin

I want the column return me the result of "Andy" & "Kelvin", which means I will only start to take the string after it read "to "

Any idea how to do do it? Thanks!

Ravi.K · Post by **Ravi.K** » Tue Jan 18, 2011 3:46 am

Try with the below derivation.

Field(InputCol[ Index(InputCol,"to ",1),(Len(InputCol)-Index(InputCol,"to ",1))+1]," ",2)

ray.wurlod · Post by **ray.wurlod** » Tue Jan 18, 2011 3:58 am

Or, slightly more simply

Code: Select all

InLink.TheField[Index(InLink.TheField, " to ",1) + 4, 999999]

This method relies on the fact that the string returned by the substring operator ends at the end of the source string no matter how many characters were actually requested in the substring operator.

Note also the spaces around the "to". This allows the field to contain something like "This store belongs to Arnold". (There's a "to" in "store".)