Solved

How to trim right trailing space in Filemaker Pro

Posted on 2008-06-21
10
1,555 Views
Last Modified: 2009-03-24
Hi All,

I have around 400 records in FileMaker Pro 9 with some fields that have extra trailing space.

How do I remove them?
I added  the following trim calculation to the field:
Trim(fieldname)
But it didnt remove any trailing space.
Thanks for any help.

A
0
Comment
Question by:akalehzan
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
how did you do this exactly? using the auto-enter feature? did you enable replacement of existing values?
or in a script?
maybe these invisibles characters are not spaces?
0
 
LVL 9

Expert Comment

by:jvaldes
Comment Utility
I would select the field that I wanted to remove the trailing spaces from and select the replace contents menu selection then in the calculation I would put Trim(Fieldname you are trimming) . I am not sure where you added the calculation but if this is a one time deal, this is how I would do it.
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
I agree with Lesouef, if the trim() function doesn't remove the extra spaces, maybe they are not really spaces after all, but some other character, like tabs.

0
 
LVL 10

Expert Comment

by:webwyzsystems
Comment Utility
jvaldes is close - but just in  case that's not a space, you can use the LEFT function.

Put cursor into field. Under the RECORDS menu, Use REPLACE FIELD CONTENTS, and replace with CALCULATED RESULT. The calculation would be:
Left(fieldName;length(fieldName)-1)

That should do it. You can use the same thing in a field calculation as well if you need it.

Hope this helps.

Rob A.
0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
What happens if you've got 2 characters to remove at the end?
I would copy the string to a binary editor, then identify the guilty characters, then use the subst function to remove them. If it is a 'tab', you can enter it in a subst function by using 'Ctrl tab' in the calc window.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:webwyzsystems
Comment Utility
Here is a corrected formula for 2,4 5 or NO extra spaces. My last post just stripped off the last character. What a noob. :-)
Do your replace with THIS calculation. Note: this will fail if your field data ends with trailing punctuation or special characters like !@#$%^&*().

MiddleWords(YOURFIELD;1;WordCount(YOURFIELD))

This seems to strip off the whitespace characters. I tested with spaces and tabs anyways....


Rob A
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
The filter() function is useful for removing non-standard characters from a string. If you know the range of possible legitimate characters that your data is made up of, you can use filter() to remove any characters that are outside that range.

The way it works is you provide a string to filter, and a string that contains all the characters that you want to be allowed.

This example will permit US upper and lower case letters, numbers, and common punctuation, but block everything else.
Result = Filter( MyString; "abcdefghijklmnopqrstuvwxyz ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*(),<.>/?" )
0
 
LVL 10

Expert Comment

by:webwyzsystems
Comment Utility
Filter() would work well for data that does not contain valid spaces. If the data contains 2 or more words, then spaces within the data would be stripped out....or the ones at the end of the data would be left on.

0
 
LVL 19

Accepted Solution

by:
billmercer earned 125 total points
Comment Utility
webwyzsystems, what I said was "The filter() function is useful for removing non-standard characters from a string." My example filter() actually did include the space character.

My example was specifically addressing the case where there are non-standard characters that need to be trimmed.

If the problem is simply to remove trailing spaces without affecting spaces between words, then the trim() function is all you need.

0
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
but you can filter, then trim to get both done if you'd keep internal spaces
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now