Jump to content

How can I trim carriage returns?

This topic is 7262 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

I'm trying to clean data that was imported into a general text field. The field contains sentences of text, but many records have trailing carriage returns.


[Text String.

Text String.


Does anyone know an efficient scripting method for removing these extra carriage returns? I have over 4000 records and would hate to have to do it manually. ??? ) Thanks for any input you can provide!


Link to comment
Share on other sites

Need more info...

If you want to remove *all* carriage returns then the Replace command will fix it easily.

If the last character is the only carriage return that you want to remove then you can do something like...

Set Field [ field , Left(Length(field)-1) ]

Link to comment
Share on other sites

i would say something like this


Set Field[FIELDNAME, Substitute( FIELDNAME , " par.gif ", " " )

Goto Record [next, exit after last]

End Loop

might work.. you may want to test it on a copy of the database first, just in case if doesn't work for some reason.



Looks like FUBAR has beat me to the reply.


[ November 30, 2001: Message edited by: bman ]

Link to comment
Share on other sites

  • Newbies

Unfortunately, the replace or substitute commands would remove all carriag returns and then the text would be a jumbled mess. I think the set field option may work. I just needed to figure out how to get to the end of the field. Once there, I can check for the carriage return and loop until I hit text. ??? ) -=>Chris

Link to comment
Share on other sites

The problem, as I see it, is not just about removing trailing newlines, but removing trailing whitespace, including spaces and tabs. FileMaker has a Trim() function, but it only works on spaces. We need a Trim() that works on all whitespace (or any other characters we choose). Let's build one.

Let's call our field "text". First we need to protect any leading whitespace, since it may be important:

"X" & text

Next we need to convince Trim() to remove newlines and tabs. Let's convert them to spaces:

Substitute(Substitute("X" & text," par.gif "," "),"<TAB>"," ")

(In Windows you press Ctrl-TAB to get a tab character in a calc; I'm not sure how you do it on a Mac.)

Now we can discard all trailing whitespace:

Trim(Substitute(Substitute("X" & text," par.gif "," "),"<TAB>"," "))

But, wait: our legitimate newlines and tabs are trashed. How to bring them back? Let's find the length of the new string, minus one for the character we added to the beginning, then take that many characters from our original string:

Left(text, Length(Trim(Substitute(Substitute("X" & text," par.gif "," "),"<TAB>"," "))) - 1)

You can use this in a calculated Replace and knock out all 4000 records in one shot--no script needed.

(I just noticed that the forum inserts spaces around par.gif . There should be no spaces around them in the calcs.)

[ November 30, 2001: Message edited by: Thom ]

Link to comment
Share on other sites

Hi friends,

There is an easier way to do this:


= RightWords(field, 9999)

That's all.

Note: This will remove any leading and trailing characters that aren't indexible - so this will be problematic if you expect to have trailing apostrophes and you want to keep them. But generally, this is the simplest method.

Also, if you want to preserve leading white space, but eliminate trailing whitespace, then there is a simple mod:


Right(RightWords("x" & field,9999), Length(RightWords("x" & field,9999))-1)

Good luck!

Andrew LeCates

[ November 30, 2001: Message edited by: droid ]

Link to comment
Share on other sites

This topic is 7262 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Create New...

Important Information

By using this site, you agree to our Terms of Use.