Text calc to choose words left of characters

[Cabinetman]

I'm trying to figure how to calc this out.

Text Field1 contains:

John Smith My top 100 greatest ideas ever

or contains

My favorite sister (who really cares?)

Text Field2 contains: I want it to show only the words left of the characters..... like ( , ....or to return the word count for use in another calc. sorry 'bout that!

So if...Text Field1 contains - John Smith My top 100 greatest ideas ever

Text Field2 contains - John Smith

Help is always appreciate.

Edited for better explanation....

Edited October 6, 2007 by Guest

[LaRetta]

Well, that is awfully vague ... there is no calculation which can fit all situations; depending no matter where to start, what character to use as break point and so on. But this might get the conversation going so we can pin down what you really need:

Let ( [

breakChar = ":" ;

stop = Position ( text ; breakChar ; 1 ; 1 )

] ;

Trim ( Left ( text ; stop - 1 ) )

)

I suppose you can use Middle() and more appropriately control where to start and end and so on ... but it is pretty-much guess work at this point.

[LaRetta]

Here is a sweet one I found from Comment. It is quite generic and able to flex nicely. It might fit your needs better (but you might have to adjust if only one break character is available:

Trim(

Let(

[

Commas = PatternCount(text; ",");

start = Position ( text; ","; 1; Commas - 1) + 1;

end = Position (text; ","; 1; Commas )

]

; Middle (text; start; end - start)

)

)

:wink2:

[Cabinetman]

I'm checking these now.

Field1 = title

Field2 = Only the words left of ? ( or ,

This is for web searching capabilities. The site I'm using this on doesn't seem to like them.

2nd Edit Some titles also have both or

some may have none of the above.................

Also can Commas=Case (statements that'll check for these characters?)

Edited October 6, 2007 by Guest

[comment]

You could use the Substitute() function to 'normalize' all the break characters you want to a single one, e.g.

Let ( [

sub = Substitute ( YourField ; [ "(" ; "," ] ; [ ":" ; "," ] ; [ "?" ; "," ] ) ;

pos = Position ( sub ; "," ; 1 ; 1 )

] ;

Case ( pos ; Trim ( Left ( sub ; pos - 1 ) ) ; YourField )

)

[Cabinetman]

You guys never cease to amaze me......

Works like a charm!

Thanks to both of you for the help.