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.

IE:

[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!

-=>Chris

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) ]

i would say something like this

Loop

Set Field[FIELDNAME, Substitute( FIELDNAME , " ", " " )

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.

----

[EDIT]

Looks like FUBAR has beat me to the reply.

[/EDIT]

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

Well you corrected my "use Replace command" mistake at least

Substitute is the command that I meant to suggest.

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

If [ Right( Text Field , 1 ) = "

sorry about that, i thought you wanted to get rid of all the carriage returns, my mistake. *smacks self in forehead*

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," "," "),"<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," "," "),"<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," "," "),"<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 . There should be no spaces around them in the calcs.)

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

Thanks All! I was able to get rid of the trailing carriage returns. The last post by Thom may be especially helpful if I decide to really clean out the text field. Thanks for all your help! ) -=>Chris

Hi friends,

There is an easier way to do this:

code:

---

= 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:

code:

---

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

---

Good luck!

Andrew LeCates

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