Kieren MacMillan recently showed me his nifty technique for removing leading and trailing carriage returns from a text field. This is a common technique for correcting data-entry errors or constructing return-separated lists. In the past, I used a simple approach:
LeftWords(MYTABLE::myfield; 999999999)
If you place this formula in an auto-enter calculation with the option to "do not replace existing value of field (if any)", it removes all carriage returns before and after text but leaves the returns between characters of text. For example, say you have the following text (invisible carriage returns have been displayed with the "¶" character):
¶
¶
John¶
Mark¶
Osborne¶
¶
When this text runs through the auto-enter calculation, it comes out looking like:
John¶
Mark¶
Osborne
After you understand how the LeftWords, RightWords, and MiddleWords functions operate, you'll understand how the leading and trailing returns are removed. The word functions extract words from a text string by identifying word separators. If a word separator isn't surrounded by letters or numbers, the function removes it completely.
This technique works great as long as the text doesn't begin or end with a word separator you want to keep, such as a period, space, comma, or the many other characters that act as word separators. For example, if you run the following text through a word function, it removes the period at the end:
¶
¶
John¶
Mark¶
Osborne.¶
¶
A more complicated formula removes leading and trailing returns but leaves other word separators. Read the comments for clarification of each part of the formula.
Let(
[@text = mytextfield; // Declare the field so it is easier to modify the formula when attaching to another field.
@stripped = Substitute(@text; "¶"; ""); // Remove all returns from the text string.
@firstChar = Left(@stripped; 1); // Grab the first character in the stripped text.
@lastChar = Right(@stripped; 1); // Grab the last character in the stripped text.
@firstpos = Position(@text; @firstChar; 0; 1); // Locate the position of the first character in the original text.
@lastpos = Position(@text; @lastChar; 0; PatternCount(@text; @lastChar))]; // Locate the position of the last character in the original text.
Middle(@text; @firstpos; @lastpos - @firstpos + 1) // Extract the text between the first and last characters.
)
In fact, you can use this technique to remove any leading or trailing characters by modifying the @stripped variable Substitute function to locate whatever characters you want.