You'd think that there would have been a built-in function to trim white space characters (<space>, <tab>, <CR>) from the beginning and end of a text string, but the Trim() function only trims spaces. The following formula will trim off all white space characters from the beginning and end of a text string, but leave any embedded ones intact.

Let([t=InputText;

tab=" "; //The literal contains a tab character

L=Left(t;Position(t;LeftWords(t;1);1;1)-1);

L1=Substitute("x"&L;[" ";"s"];[

You mentioned it in the end, why not use:

Substitute ( MiddleWords(Substitute ( Input ; "." ; "

How does one type the "

Option o on a Mac. Probably not doable on a XP.

LOL

Lede

How does one type the "

So just for giggles S

Ahem....

Just to get back to the original discussion.

For S

Bob, I'm interested in using your formula to 'clean' data... I'm wondering if you could upload an FMP file with it embedded that I could just copy/paste from? (My browser window drops all special characters from your formula, rendering it incomplete.) Thanks,

Jim

Edited August 22, 200520 yr by Guest

Do you have developer/advanced? If so, you can use a recursive function such as:

TrimP(text)=

// TrimP strips spaces and carriage returns from text

Case(

Left(text;1)="¶" or Left(text;1) = " ";

TrimP(Right(text;Length(text)-1));

Right(text;1)="¶" or Right(text;1) = " ";

TrimP(Left(text;Length(text)-1));

text)

This just removed paragraph marks and spaces, but you can set it to get rid of whatever you want.

Jeff

Hi Jim,

Sorry it took so long to reply. I've been away. Here is an example file.

WhitespaceTrim.fp7.zip

ø = alt+0248 ;)