Solved

How to trim right trailing space in Filemaker Pro

Posted on 2008-06-21
10
1,606 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
ID: 21839921
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
ID: 21842743
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
ID: 21846705
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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 10

Expert Comment

by:webwyzsystems
ID: 21852063
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
ID: 21852611
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
 
LVL 10

Expert Comment

by:webwyzsystems
ID: 21852901
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
ID: 21857895
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
ID: 21872143
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
ID: 21874312
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
ID: 23965467
but you can filter, then trim to get both done if you'd keep internal spaces
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

776 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