7 Replies Latest reply on May 8, 2012 1:57 PM by TomHays

# Calculation to filter out words after ":" or "-"

Hello,

I am an amateur when it comes to formulating calcualtions. Mostly through trial and error do I get things right. So I was wondering if someone knows how to do this faster than I do. Here is what I'm trying to do:

I need a calculation to search in the Title field for ":" and "-" and if the field has either character I need all words that follow it to be excluded.

Example: (Title field) William Teller: A short story >> (calculation output) William Teller

• ###### 1. Re: Calculation to filter out words after ":" or "-"

Try this one.

-Tom

Let(

[

theString = Title;

thePos1 = Position(theString; ":"; 1; 1);

thePos2 = Position(theString; "-"; 1; 1)

];

Case(

(thePos1 = 0) and (thePos2 = 0); theString;

(thePos1 = 0); Left(theString; thePos2 - 1);

(thePos2 = 0); Left(theString; thePos1 - 1);

thePos1 < thePos2; Left(theString; thePos1 - 1);

Left(theString; thePos2 - 1)

)

)

• ###### 2. Re: Calculation to filter out words after ":" or "-"

Try this.

Case (

PatternCount (Title ; ":" ) > 0;

Left (Title ; Length (Title ) - Position (Title ; ":" ; 1 ; 1 ) -1 );

PatternCount (Title ; "-" ) > 0;

Left (Title ; Length (Title ) - Position (Title ; "-" ; 1 ; 1 ) -1 );

)

• ###### 3. Re: Calculation to filter out words after ":" or "-"

Thanks Tom! This is just what I was looking for. I can see that my time would've been wasted because I don't think I would ever come up with that on my own.

Is the spacing in your function a standard format for complex calculations?

I wonder if you could help me with another calculation:

If I wanted to search my Vendor field for entries that contain a ";" can I transfer all words before this character to Vendor1 field and all words after this character to Vendor2 field?

Example: (Vendor field) Amazon; City Book Store >> (Vendor1) Amazon AND (Vendor2) City Book Store

• ###### 4. Re: Calculation to filter out words after ":" or "-"

m.bodily.consulting wrote:

Is the spacing in your function a standard format for complex calculations?

I don't know that the format I used is a standard. I just like to format my calcs to make it readable and easy to edit.

Note that the calculation is a bit elaborate to accommodate strings that have both ":" and "-". When they are both present it separates the string at the earlier one.

-Tom

• ###### 5. Re: Calculation to filter out words after ":" or "-"

m.bodily.consulting wrote:

If I wanted to search my Vendor field for entries that contain a ";" can I transfer all words before this character to Vendor1 field and all words after this character to Vendor2 field?

Example: (Vendor field) Amazon; City Book Store >> (Vendor1) Amazon AND (Vendor2) City Book Store

You would need two calculations. One for the Vendor1 field and another for the Vendor2 field.

Vendor1 =

Let(

[

theString = Vendor;

thePos = Position(theString; ";"; 1; 1)

];

Case(

thePos;Left(theString; thePos - 1);

theString

)

)

Vendor2 =

Let(

[

theString = Vendor;

thePos = Position(theString; ";"; 1; 1)

];

Case(

thePos;Right(theString; Length(theString) - thePos);

""

)

)

If you desired, you could simplify the second calculation by using the already-calculated value of Vendor1, but I prefer to keep them independent so that you can calculate them in either order.

-Tom

• ###### 6. Re: Calculation to filter out words after ":" or "-"

I did notice the priority of stings. However, looking through more of my records I noticed that identifying the "-" character has made my titles appear incomplete. How do I delete the string parts that apply to the "-". I tried to do this on my own and I did it wrong because sometimes a number or nothing appears instead of text.

• ###### 7. Re: Calculation to filter out words after ":" or "-"

m.bodily.consulting wrote:

How do I delete the string parts that apply to the "-".

I think you are asking for a calc that only considers ":". This would essentially be the same as the calc you asked for that handles ";".

Let(

[

theString = Title;

thePos = Position(theString; ":"; 1; 1)

];

Case(

thePos;Left(theString; thePos - 1);

theString

)

)