Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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
doc filter not begin 1 doc filter not begin 2

Hide and mask cell contents with asterisk with Format Cells

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.
doc filter not begin 3 doc filter not begin 4

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.
doc filter not begin 5

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.

doc filter not begin 6

doc filter not begin 7

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

ot move

Do You Want To Have A Pay Raise and Much Time To Accompany With Family?

Office Tab Enhances Your Efficiency By 50% In Microsoft Office Working Right Now

Unbelievable, working at two or more documents is easier and faster than working at one.

Compareed with well-known browsers, the tabbed tool in Office Tab is more powerful and more efficient.

Reduce hundreds of mouse-clicks and keyboard typing every day for you, say goodbye to the mouse hand now.

If you usually work at multiple documents, Office Tab will be a great time-saver for you.

30-day free trial, no credit card required.

Read MoreFree Download Now


Filter data does not begin/end with special characters by helper column

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:

=LEFT(cell_reference,character_num)="specialchar"

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:

=RIGHT(cell_reference,character_num)="specialchar"

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:
doc filter not begin 8 doc filter not begin 9

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.
doc filter not begin 10


Filter data does not begin/end with special characters by Super Filter utility

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.

Boots Your Excel With One Excel Suit
300+ professional and easy-used tools for Excel 2019-2003

After free installing Kutools for Excel (60-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.
doc filter not begin 11

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.
doc filter not begin 12

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.


Other Operations (Articles) Related To Filtering

Filter data based on a list
This tutorial provides tricks on filtering data based on a given list data in Excel.

Filter data contains asterisk
As we know, we use asterisk mask to stand any characters when you filter data, but how can you do if you want to filter data which contains asterisk mask? Now this article introduces the method about data filtering if containing asterisk or other special characters in Excel.

Filter data with criteria or wildcard
If you want to filter data with multiple criteria, how can you do? This tutorial is talking about how to set multiple criteria and filter data in Excel.


Kutools for Excel - The Best Office Productivity Tool Increase Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 60-day free trial.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.