Skip to main content

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.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations