#### Purpose

The **OCFilter** function is useful to work around the maximum forumla length limit of Excel by allowing you to break a single formula into smaller pieces. The **OCFilter** function was originally created to address Office Connector formulas with complex filter arguments, but it can be used for any text argument in any Excel formula.

#### Syntax

**OCFilter**( *filter template* , *enclosers* , *arg1* , [ ... *argn* ] )

The **OCFilter** function syntax has these parts:

Argument | Description |
---|---|

filter template | Required. This argument specifies a pattern containing literal text and replacement markers. |

enclosers | Required. This argument specifies how your filter template will identify replacement markers. |

arg 1 ... arg n | Optional. This is a comma-delimited list of values used to replace the markers in the filter template. Quite often you will use cell references to columns on the current row that contain key values for your filter. If no arguments are specified, then the filter template is returned unchanged. |

#### Remarks

Replacement markers refer to the ordinal position of the associated argument. Use the enclosers to identify that a number N is a marker for the Nth argument. For example:

**OCFilter***("My [1] has [2].", "[]", "dog", "fleas")*

returns

*My dog has fleas.*

because the *enclosers* argument indicates that any number enclosed in square brackets *[ ]* is a marker that refers to one of the *args*, so *[1]* refers to *dog* and *[2]* refers to *fleas*.

#### Filter Example

This example demonstrates how you could use the **OCFilter** function to return a filter that matches records where the cost code is *1-045* and the job number is specified for each row in column A. You could then use the returned value as the *filter* argument in other functions like TSSum and TSCount.

**OCFilter***("PJOB='[1]' AND [PHASE]='[2]'", "[]", $A1, "1-045")*

If cell $A1 contains *03-001*, then the formula above returns:

*PJOB='03-001' AND PHASE='1-045'*

#### Inserting a Formula

To enter an** OCFilter** function, click on the **Insert Function** button (to the left of the Excel formula bar), select the *Office Connector 2.x* category, select **OCFilter** and click **OK**. You can then type the **FilterTemplate**, the enclosers and type values or click on cells for each of the **args**.