I have a text field where I want to be able to remove any carriage returns which are at the end of the text (while keeping carriage returns which are in the middle). To get rid of one is easy:

case(right(field;1)= "¶" ; left (field;length(field)-1)

However, there may be more than one carriage return at the end of the text. It is quite common to have 2 or 3, but on occasions there have been 20+.

So, is there a tidy way to remove all these extra carriage returns using a calculation?

An example of the text in the field (using ¶ as a carrriage return)

"Price Guide: £20¶

double £40¶

suite $80¶

¶

¶

¶"

In the above text example I would want to remove the final four paragraph characters.

I have a recursive custom function to do just that.

xCF_RemoveFormat_Full ( text )

Let( [ Format = Trim ( TextFormatRemove ( If ( Right ( Text ; 1 ) = "¶" ; Replace ( Text ; Length ( Text ) ; 1 ; "" ) ; Text ) ) )



];



If ( Right ( Format ; 1 ) = "¶" ; xCF_RemoveFormat_Full ( Format ); Format ) )

Edited April 16, 200718 yr by Guest

Substitute ( Trim ( Substitute ( field ; [ " " ; "§" ] ; [ ¶ ; " " ] ) ) ; [ " " ; ¶ ] ; [ "§" ; " " ] )

Those using v7 can also use the following which was originally posted by comment back in '05.

That's a neat idea.

Or you could use PatternCount() to deal only with the last occurence, something like:

Let ( [

crRun = "����������" ;

textCR = text & crRun ;

last = PatternCount ( textCR ; crRun ) ;

pos = Position ( textCR ; crRun ; 1 ; last )

] ;

Left ( textCR ; pos - 1 )

)

Note: n carriage returns in 'crRun' will remove up to n-1 carriage returns - always at the end.

full topic can be found at: http://fmforums.com/forum/showtopic.php?tid/168001/post/168343

Thanks for that solution strangler. Never knew could do recursive custom functions! Has opened up a whole new world of possibilities!