Brian Dunning's FileMaker Custom Functions

Trim4 ( text )

Removes *all* leading and trailing white space from the supplied text, including tabs, carriage returns, spaces and non-breaking spaces.

  Average rating: 4.0 (72 votes) Log in to vote

Ray Cologon   Ray Cologon - Show more from this author
NightWing Enterprises
http://www.nightwing.com.au/FileMaker

Share on Facebook Share on Twitter

  Sample input:
Trim4 (" ¶¶Your text here ¶ ")
  Sample output:
Your text here

  Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

FileMaker's built-in Trim( ) function does a great job of removing spaces from the beginning or end of a value, but it does not deal with other types of 'white space' characters such as tabs and carriage returns.

Other functions such as the LeftWords( ) function will remove tabs abnd CRs, but also take out punctuation, leading decimals, bullets and dashes etc.

This function operates recusively to remove any or all of the four characters:

- space
- non-breaking space
- carriage return
- tab

from leading and trailing positions around the supplied text. I've named it Trim4 because it explicitly removes leading/trailing occurrences of four characters.

Note1: The first parameter of each of the PatternCount( ) functions contains the four targeted characters (space, nbrspace, tab, CR).

Note2: To address an issue that occurred when copying the original function (some browsers rendered non-breaking space as regular spaces), a revised version has been posted above. This version requires FIleMaker 10 or later, as it uses the Char( ) function to explicitly render the characters mentioned in Note 1 above.

Note3: If you wish to use the revised version of the Trim4( )function with a version prior to 10, you'll need to re-define the sT variable with a literal string containing the corresponding characters (tab, return, space and non-breaking space respectively). Or find someone with a copy of an earlier version of the function... ;)

 

Comments

Paul   Paul, Peak 14 LLC
Dec 8, 2010
Hi,

this is a great function, but has anyone else noticed that if the last two letters of the field are the same, such as "LL", the trim4 function will also remove the last "L".

Any ideas?

Thanks.....Paul
 
Ray Cologon   Ray Cologon, Melbourne
Dec 8, 2010
Hi Paul,

I just tested your claim in four different files that have the Trim4 CF installed. When I enter multiples of the final letter none of them were removed by Trim4 in any of the files.

I suspect there is something else going on with your implementation.

Regards,
Ray
 
Ray Cologon   Ray Cologon, Melbourne
Dec 9, 2010
Paul,

On further reflection, I suspect your problem may arise from a failure to pay heed to "Note 2" in the description above when installing the function in your file. If you have two spaces in the PatternCount( ) string rather than a space and a non-breaking space, then of course the function will not operate correctly (and will erroneously trim extra characters whether or not they are repeated letters).

You might like to check and see if this is what lies behind the behavior you're seeing.

All the best,
Ray
 
Ted Stein   Ted Stein, Washington, DC
Mar 9, 2011
Just so everyone knows: non-breaking spaces (at least on a mac) are alt+space.
 
Pavle   Pavle, NYC
Jun 23, 2011
Thanks Ted,
your non breaking space info saved me a lot of headache.
 
Dan Pouliot   Dan Pouliot, Raymond, NH
Sep 13, 2011
This function looks hopeful but it does not seem to be removing Windows returns. ;-(
 
Ray Cologon   Ray Cologon, Melbourne
Sep 13, 2011
Hi Dan,
I've seen this function working on several hundred Windows systems over the last seven years or so, and it has removed Windows returns in every case.

I'm not sure what has given rise to a problem in your case, and you haven't given us much to go on.
 
Justin Grant   Justin Grant, Sydney
Mar 7, 2012
Hi Roy,

We've been using your function but are experiencing the issue where odd numbers of spaces make the function actually remove some of the entry.

I read on another forum that using Char instead of the actual characters would work - but I can't actually get that to work. (http://fmforums.com/forum/topic/68841-trim4-problem-or-my-use-of-it/)

Any suggestions?

I've tried entering the nbsp into the function by copying a nbsp from word into the function - but that didn't seem to work. I cannot type a nbsp into the function - do FM functions not accept nbsp on windows!?
 
Ray Cologon   Ray Cologon, Melbourne
Mar 8, 2012
Hi Justin,

The fact that part of the content was being removed when trimming spaces points to your implementation of the function having multiple spaces (character 32) rather than a space and a non-breaking space (character 160) in the parameter.

I've now posted a revised version of the function above, using the Char( ) function introduced in FileMaker 10 to circumvent the issue. As long as you don't need the function to work with v9 or earlier this should not be a problem.

Regards,
Ray
 
trev hatchett   trev hatchett, manchester
Mar 24, 2012
hi ray

thanks for this handy function. whitespace characters can be tricky to identify. you might like to extend your function to include other whitespace characters commonly found in text that originates in publishing:

char ( 9 ) = horizontal tab
char ( 10 ) = new line
char ( 11 ) = vertical tab
char ( 12 ) = new page
char ( 13 ) = return
char ( 32 ) = space
char ( 160 ) = non-breaking space
char ( 8194 ) = en space
char ( 8195 ) = em space
char ( 8201 ) = thin space

best wishes

T
 
Carl   Carl, Calgary
Apr 3, 2012
Hi Ray,

Since I found this function last month, I have used it in a whole bunch of databases. Many thanks!
 
Rich   Rich, Ma
Aug 29, 2012
Hello,

Dumb moment! When I add this to the Custom Functions and click ok it gives a message that highlights text and says field parameter can not be found.

What parameter should I be using? I am trying to trim an email address field?

Thanks,
Rich
 
Ray Cologon   Ray Cologon, Melbourne
Aug 29, 2012
Hi Rich,

The syntax of the function is given in the comment on the first line. Ie it requires only one parameter which is named 'text'.

If you define a parameter with that name, the function definition should then be accepted without error.

Regards,
Ray
 
Rich   Rich, Ma
Aug 29, 2012
Ray,

That worked!


Thanks,
Rich
 
Pablo Ruiz   Pablo Ruiz, Mexico City
Sep 24, 2012
If you're trying to use this on FM Pre-10, you can replace the text after St= with the appropriate characters (these are on the Mac, I don't know how you get them on windows) by typing a space (gives you normal space), then alt+space which gives you a non-breaking space, then alt+tab which gives you the tab character and then copying and pasting the "enter" character. You can get that defining any calculation, just insert and copy it.
 
Eric Pardee   Eric Pardee, Burbank, CA
Sep 26, 2012
Worked as described, thanks!
 
Paul Houck   Paul Houck, Dallas, TX
Oct 8, 2012
This is my first time dealing with custom functions. I have replaced the 5 instances of "Text" with my field name and defined that as my only parameter in the custom function. I'm trying to use it with Replace Field Contents but nothing changes in the field which has a trailing carriage return. What am I missing?
 
Paul Houck   Paul Houck, Dallas, TX
Oct 8, 2012
Working now, it was one of the first things I looked at after the above post. Of course! Thanks
 
Geo Howard   Geo Howard, Sebastopol, CA
Nov 30, 2012
This worked perfectly, thanks!!
 
Colin Purnell   Colin Purnell, Blue Mountains
Dec 2, 2012
Thank you, thank you, thank you.
 
Geoff Tonge   Geoff Tonge, Cambridge University Press
Feb 26, 2013
Many thanks, this saved me a lot of time.
 
Manjit Behera   Manjit Behera, Bhubaneswar, Odisha, India
Apr 9, 2013
This is a nice custom function to remove special characters from the text. When we are coping/importing some web text into our FM database we need to trim out many special characters in that case this custom function will show a great result.

Thanks,
Manjit Behera
Software Developer, Mindfire Solutions, India
http://www.mindfiresolutions.com
FileMaker11 Certified Developer
 
Juan Carlos Ramirez   Juan Carlos Ramirez, Bogotá
Nov 27, 2013
Big thanks. Processed more than 100k recs in a few minutes !
 
Cameron Knowlton   Cameron Knowlton, Victoria, BC
Dec 6, 2013
Brilliant, thank you, thank you, thank you!
 
Fed   Fed, Toronto
Mar 27, 2014
I have used your function in the past. Now, with FMP13, it seems to be off. The number of characters it additionally deletes is variable. Are you having trouble with it as well?

Thanks,

Fed
 
Ray Cologon   Ray Cologon, Melbourne, Australia
Mar 27, 2014
No problem here, nor on a dozen or more sites I'm aware of that are using it with FileMaker 13.

Is the version of the function you're using the same as the one that appears here? What 'extra' characters is it deleting, and under what exact conditions?
 
Michael   Michael, Ostfildern, Germany
Mar 31, 2014
same problem here....
Function doesn't work with FMP13
before, with FMP12 everything was fine
 
Ray Cologon   Ray Cologon, Melbourne, Australia
Apr 1, 2014
Hi Michael,

As I mentioned in my previous post, I have been unable to reproduce the problem you're reporting, and have instances of this CF working fine on FileMaker 13 (Mac, Windows, Go, Server and Webdirect). Moreover I've tested it offline in FMP13 with several hundred strings containing assortments of leading and trailing characters and have not seen any misbehavior.

It would be helpful if you or Fed would answer the two specific questions I asked in my previous message:

• Is the version of the function you're using the same as the one that appears here?
• What 'extra' characters is it deleting, and under what exact conditions?

By "what exact conditions", I mean what application on what system and hardware, how and where the function is being evaluated (in a script, in schema etc), what precise input string you are passing to it when it fails, and what exact output it returns.

Regards,
Ray
 
Fed   Fed, Toronto
Apr 11, 2014
This new version works fine. I had a really old one that included the raw characters, not the char(). I think that FMP13 handles special charters differently (maybe).

Thanks!
 
Rialto   Rialto, Europe
Apr 15, 2014
Hi Ray, do you think you might modify it at some point to also include a Paragraph character. As far as I can see, that's not possible now, and I am getting quite a few of them in imports involving excel and/or numbers. I 'll try my hand at it too.
 
Ray Cologon   Ray Cologon, Melbourne, Australia
Apr 15, 2014
Hi Rialto,

If by "paragraph character", you mean a carriage return (Char(13), Trim4 already does that (so I am guessing you mean something else...).

If you mean a pilcrow ("¶" - which is Char(182)) or some other character, yes it would be possible address that by adding it to the list of target characters defined in the first line of the Let( ) statement (e.g. in the variable string (sT = ...).

However the function as posted here is explicitly called "Trim4" because it targets a specific four characters, and that's fairly widely understood - so variants I have created (there have been quite a few of them, though they aren't posted here ;)) that target additional characters have also been renamed. ;)

Regards,
Ray
 
Mike Scott   Mike Scott, Liverpool
Jul 10, 2014
Excellent - just what I needed!
 
abhaya   abhaya, BBSR
Aug 22, 2014
Yes, its a really a nice one. But sometimes we need a specific no of carriage retuns from our text (not at leading and trailing ). lets look at the below example.
e.g
"1¶2¶3¶4¶4¶¶4¶¶¶¶¶¶¶¶5¶¶¶¶¶¶¶¶¶¶¶¶6¶¶¶¶" text needs to be converted to "1¶2¶3¶4¶4¶4¶5¶6".
So for this we have to use one additional function to do the same.

Function: Substitute ( Text ; ["¶¶" ; "¶#"];["#¶";""] ;["#";""] )
Where "Text" is the input string.

It only replces the multiple carriage retuns within the text to a single one. If will not replaces the leading and trailing carriage returns. SO we have to pass this functin into the Trim4(text) custom function.
 
Dan Shockley   Dan Shockley
Mar 31, 2015
What are your thoughts on the similar non-recursive method used in Supertrim, by Debi Fuchs, found at http://www.briandunning.com/cf/904 ? I'm wondering if you know of an advantage to the algorithm used here?
 
Ray Cologon   Ray Cologon, Melbourne, Australia
Apr 1, 2015
Hi Dan,

The function Debi came up with is great and, since it exploits native functions, would work well for heavy lifting applications (i.e. processing large amounts of text).

The Trim4 example shown here is a subset of a suite of functions including a master function (not published here) that uses the same concept to trim any string of characters from leading/trailing positions (by supplying the sT variable at runtime via a second function parameter) - so in that sense the recursive approach I took here offers some added flexibility and the ability to more easily reapply the logic to other related tasks.

Both have their uses and together they demonstrate the flexibility of the platform (i.e. there are multiple viable solutions to most problems in FileMaker).

Regards,
Ray
 
Paco Baez   Paco Baez, Adeliade, Australia
Jul 21, 2016
Simple and elegant, what else could we ask? =D
Ta
 
Ben G   Ben G
May 20, 2020
Great function! Use it on auto-enter to clean up every field that a user fills in. One thing I added to this was to remove char(0). Ran into this when copying and pasting from Acrobat. FileMaker char(0) has an issue that just returns blank so oddly enough have to remove using " Base64Decode ( "AA==" )" so added to the end of the line:

sT = Char(9) & Char(13) & Char(32) & Char(160) & Base64Decode ( "AA==" ) ;
 
flusheDData   flusheDData, n/a
Jul 23, 2023
Hi, let me share a non recursive version of this awesome function. I have used Trim4 for ages and still do. I just came up with this.
https://www.briandunning.com/cf/2685
 

Log in to post comments.

 

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

Support this website.

This library has been a free commmunity resource for FileMaker users and developers for 20 years. It receives no funding and has no advertisements. If it has helped you out, I'd really appreciate it if you could contribute whatever you think it's worth: