"I need to remove the ---and the data here and again the trailing--- but keep the data before the first ---."
But your example data doesn't have any data before the first --- as those are the first three characters entered in the field...
If you want just Ford Fiesta returned from the above example. You can nest some functions to extract just that text:
Left ( TextField ; Position (TextField ; "---" ; 1 ; 2 ) - 1 )
Will return ---Ford Fiesta, so we just need one more function to strip off the leading dashes.
Let ( S = Left ( TextField ; Position (TextField ; "---" ; 1 ; 2 ) - 1 ) ; Right ( S ; Length ( S ) - 3 ) )
Phil thanks for the answer
there is data before the first --- it has a name of a business and then the first ---. The amount of words and characters before the first --- is random and has no pattern. I just want to delete the --- ford fiesta--- and leave all the data before the ---ford fiesta--- alone.
In that case:
Left ( TextField ; Position (TextField ; "---" ; 1 ; 1 ) - 1 )
will return everything to the left of the first --- in the field.
Thanks Phil, it worked perfectly!!