Hi guys,

Has anyone every come accross a formula that will text format a field like so

The user inputs:

my name is david.i live in a town.Please vist me

And the format output from the formula

My name is david. I live in a town. Please vist me

All i want really is the first letter of every sentence capitalized and there be a space after every .

Any help very much appriated.

D

I don't know about doing it by calculation, but with an additional field, a global number field called counter, I think this script would do it for you:

Set Field [ table::counter; 1 ]

Set Field [ table::field; Upper ( Left ( table::field; 1 ) ) & Right ( table::field ; Length ( table::field ) - 1 ) ]

If [ PatternCount ( table::field ; "." ) ]

Loop

If [ Middle ( table::field ; Position ( table::field ; "." ; 1 ; table::counter ) + 1 ; 1 ) <> " " ]

Set Field [ table::field; Replace ( table::field ; Position ( table::field ; "." ; 1 ; table::counter ) ; 1 ; ". " ) ]

End If

Set Field [ table::field; Let (

[ PositionCurrentPeriod = Position (table::field ; "." ; 1 ; table::counter ) ;

TextToCurrentPeriod = Left (table::field ; PositionCurrentPeriod) ;

StartingWord = WordCount ( TextToCurrentPeriod ) + 1 ;

WordAfterPeriod = MiddleWords (table::field ; StartingWord ; 1 ) ] ;

Substitute ( table::field ; WordAfterPeriod ; Proper ( WordAfterPeriod ) )

) ]

Set Field [ table::counter; table::counter + 1 ]

Exit Loop

If [ table::counter = PatternCount ( table::field ; "." ) + 1 ]

End Loop

Else

Set Field [ table::field; Replace ( table::field ; 1 ; 1 ; Proper ( LeftWords ( table::field ; 1 ) ) ) ]

End If

You could use the substitute function like this:

Middle(Substitute("."&InputText;[" ";" "];[". ";"."];

[".a";". A"];[".b";". B"];[".c";". C"];[".d";". D"];

[".e";". E"];[".f";". F"];[".g";". G"];[".h";". H"];

[".i";". I"];[".j";". J"];[".k";". K"];[".l";". L"];

[".m";". M"];[".n";". N"];[".o";".O"];[".p";". P"];

[".q";". Q"];[".r";". R"];[".s";". S"];[".t";". T"];

[".u";". U"];[".v";". V"];[".w";". W"];

[".x";". X"];[".y";". Y"];[".z";". Z"]);3;10000000)

SWEET!! I didn't know you could pass multiple parameters like that!

Incidentally, you kill all the spaces. Take out the [" ";""] parameter, and you're good to go though, except for the "existing space after period" issue. I'm going to have to work the brackets in other functions and see what happens.

I love this forum...

Yeah, I noted the glitch after I posted. I went back and edited the previous post to fix it. But, you must have already got the old one. The intention was to replace double blanks with a single one, because excess spaces could mess up the calculation.

The ability to use multiple sets of search/replace strings in the Substitute function is new with version 7. With version 6 and earlier, you can still accomplish the same thing by nesting Substitute functions, but it's not as clean looking.

TrimAll( text; 0; 0 ) will change any multiple spaces into single ones and remove any leading and trailing ones. Just an FYI.

This newfangled version 7 has been sitting in the box here for the past 10 months. So, now that I finally got it installed, I'm finding all sorts of clever new functions. Had a look at the TrimAll function a couple of weeks ago, but for some reason I didn't think it would do that. Thanks for pointing it out.

Noticed there's still little problem with the formula in a couple of situations. Here is a fixed version:

Middle(Substitute(TrimAll(". "&Substitute ( InputText ; "." ; ". " );0;0);

[". a";". A"];[".b";". B"];[".c";". C"];[".d";". D"];

[". e";". E"];[". f";". F"];[". g";". G"];[". h";". H"];

[". i";". I"];[". j";". J"];[". k";". K"];[". l";". L"];

[". m";". M"];[". n";". N"];[". o";".O"];[". p";". P"];

[". q";". Q"];[". r";". R"];[". s";". S"];[". t";". T"];

[". u";". U"];[". v";". V"];[". w";". W"];

[". x";". X"];[". y";". Y"];[". z";". Z"]);3;10000000)

No wonder "Using FileMaker 7" is about a foot and a half thick and weighs around 150 pounds.

Hey! My copy doesn't even say anything about TrimAll! It's got a Trim, and a TrimChar (which would do the trick too), but no TrimAll. I guess if they included everything, it'd be 2 feet thick and 200 pounds.

TrimChar is not a function in FM 7, unless it's possibly for a different region/language?

Oh, never mind. I looked again, it's an example of a custom function. They done made it up...

Still no mention of TrimAll, though. What do the TrimSpace and TrimType parameters mean?

Hi guys thanks for all the input its great really works a charm.

here is a newer vesion as b c d were formatted funny last time.

fmforums rule.

D

Middle(Substitute(TrimAll(". "&Substitute ( Input Text ; "." ; ". " );0;0);

[". a";". A"];[". b";". B"];[". c";". C"];[". d";". D"];

[". e";". E"];[". f";". F"];[". g";". G"];[". h";". H"];

[". i";". I"];[". j";". J"];[". k";". K"];[". l";". L"];

[". m";". M"];[". n";". N"];[". o";".O"];[". p";". P"];

[". q";". Q"];[". r";". R"];[". s";". S"];[". t";". T"];

[". u";". U"];[". v";". V"];[". w";". W"];

[". x";". X"];[". y";". Y"];[". z";". Z"]);3;10000000)