I have a text field containing reports imported from an electronic patient record DB.

 

These reports have tons of open space between paragraphs etc, making reading difficult (much unnecessary scrolling).

 

There are no excess space characters, and trim() has no effect (many records start with multiple empty lines).

 

Presumably there are invisible <CR> characters all over the place, but I can't strip them out with substitute (field; "¶¶" ; "") or anything similar.

 

Any suggestions on how to edit invisible characters with the calc engine?

 

 

thanks!

Try the Trim4 available at Brian Dunning's site Link

Thanks Lee, I'll give it a try, but that is only designed for leading and lagging white space.

 

I need something for white space inside as well...

Try =

Let (
prep = Substitute ( text ; [ " " ; "§" ] ; [ Char ( 10 ) ; " " ] ; [ ¶ ; " " ] )
;
Substitute ( TrimAll ( prep ; 0 ; 0 ) ; [ " " ; ¶ ] ; [ "§" ; " " ] )
)

Thanks, that Char function is what I was looking for!

 

Is there a handy place to look up the Unicode numbers? How do you combine several numbers in a string?

 

Char (10)(xx)(yy)  ?

Your help in FileMaker "Code"

 

http://fmhelp.filemaker.com/fmphelp_11/en/html/func_ref3.33.46.html

thanks!

Is there a handy place to look up the Unicode numbers?

 

This is an especially nice one: http://unicode-table.com/en/

I like that one, thanks for providing the link.