Three quick ways to filter cells does not begin / end with a string in Excel

Supposing you want to filter cells in column A which do not begin or end with special characters as below screenshot shown, how can you quickly solve it in Excel sheet? This article will introduce three easy ways to handle this job.

Not begin with KTE	Not end with 1

• Filter data does not begin/end with special characters by Filter function
• Filter data does not begin/end with special characters by helper column
• Filter data does not begin/end with special characters by Super Filter utility
• Other relatived articles (operations) about filtering

In Excel, you can apply the Filter feature to filter data which does not begin or end with special characters.

1. Select the range you want to filter, then click Data > Filter to enable the Filter function.

2. Then click at the Filter icon in the column you want to filter data, then in the drop down menu, click Text Filters > Custom Filter.

3. In the Custom AutoFilter dialog, select does not begin with or does not end with criterion from first drop-down list as you need, then type the special characters you want to filter based on in the next to textbox.

4. Click OK. Then the data has been filtered.

In Excel, you also can create a helper column to solve this job.

1. Select a cell next to the data range, take C2 for instance, use one of below formula

Judge if the cell begins with special characters:

For instance, judge the cell A2 if begins with KTE, use this formula =LEFT(A2,3)="KTE"

Judge if the cell ends with special characters:

For instance, judge the cell A2 if ends with 1, use this formula =RIGHT(A2,1)="1"

2. Select the formula cell, and drag its AutoFill Handle down to other cells in this column. Now you have applied the formula to the whole column. See screenshot:

3. Then select one of the formula columns you want to filter, click Data > Filter, click at Filter icon to choose FALSE only in the drop-down menu, and click the OK button. Now the data which does not begin or end with special characters in column A is filtered out.

There is a utility – Super Filter of Kutools for Excel which can directly filter data does not begin/end with a string, also can achieve the operation the built-in Filter function cannot do.

After free installing Kutools for Excel (30-day free trial), please do as below steps.

1. Select the data range you use, and click Kutools Plus > Super Filter to display the Super Filter pane.

2. In the Super Filter pane, create the filter criteria by choosing the column header, Text and Does Not Begin With or Does Not End With, then type the characters you will filter based on in the textbox, and click Ok to define the criteria.

3. Click Filter button, and then the data will be filtered.

Tip:

1. To clear the filter, click Clear button in the Super Filter pane.

2. You can filter data with multiple criteria as you need by using Or or And relationship.

3. Super Filter utility also can filter data by date/year/month/text format and so on. Click here to know more about Super Filter.