Removing white space in text fields is nothing new for FileMaker, but removing extra space before carriage returns ( “¶” for short ) from a delimited list, requires a little bit more than the simple trim function that is provided in FileMaker.

The trim function in FileMaker will remove unneeded spaces at the beginning and end of a text string. This is very helpful for a single line of text. It will take a line of text like
” Bob ” and turn it into “Bob”.

Unfortunately when you have several lines of text that are delimited by a ¶ character as in the following example, you will need something else.

example:

“Allison¶
Bob ¶
Charlie¶
Doug”

If you want to make sure none of the lines have an extra space at the end like the second line “Bob ¶”, a simple trim will not work.

This is where the substitute function can come in handy. 

Substitute(text;searchString;replaceString)

The Substitute Function will search for a character or string and replace it with whatever character or string you choose. With this you can take a text field like the example above and use the substitute function to replace the occurrences of ” ¶” with a single “¶”.

 Substitute ( example ; ” ¶” ; “¶” );
(notice there is a space before the ¶ in the searchString)

This will provide you with the corrected:

“Allison¶
Bob¶
Charlie¶
Doug”

You can apply the same substitute several times to remove multiple spaces, and when mixed with the trim function, it can really help clean up text and lists.

Substitute is one of my favorite functions for working with text. If you haven’t used it before, give it a try and see if it can’t help remove unwanted white space.

 -Xandon