Using the #Excel Advanced Filter to a List Having Internal Numbers in a String By David Hager

The Excel advanced filter is likely the most underused of all Excel features relative to its power to analyze data. Great examples of the use of the advanced filter feature have been published by Tushar Mehta, Debra Dalgleish and Charley Kyd respectively, who not coincidentally have all been Microsoft Excel MVPs.

http://www.tushar-mehta.com/publish_train/data_analysis/06.shtml

http://www.contextures.com/xladvfilter02.html

http://www.exceluser.com/formulas/search-list-with-sumproduct.htm

Update:

There is another reference I wanted to include in the post, but just found. It is:

http://www.get-digital-help.com/2016/12/20/filter-rows-where-a-cell-contains-a-numeric-value/

I could not find an example in my Internet search of a way to filter based on strings with internal numbers in a column list. So, I decided to fill that void. There can be multiple criteria in an advanced filter, in this case only is needed. Given that A5 is the first item in the list, the following formula can be used.

=OR(NOT(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5))))

The figure below shows how the advanced filter was set up.

 afn1

This is based on the assumption that all of the strings in the list would contain internal numeric strings or nothing at all. The result is:

 afn2

If necessary, the following formula will filter all strings containing no numbers and those where the first or last character are numbers.

=AND(NOT(ISNUMBER(VALUE(LEFT(A5,1)))),NOT(ISNUMBER(VALUE(RIGHT(A5,1)))),OR(NOT(ISERROR(FIND({0,1,2,3,4,5,6,7,8,9},A5)))))

The result is:

afn3

Since this example assumes only 1 internal string of numbers in each string, it cannot handle (filter out) a case such as ae326tw207def. Perhaps I will tackle that scenario in a future post. Enjoy!

You can download the file here.

AdvFiltNumbers

Advertisements

4 thoughts on “Using the #Excel Advanced Filter to a List Having Internal Numbers in a String By David Hager

  1. Pingback: #Excel Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

  2. Pingback: #Excel: Using Advanced Filter with a Custom List By David Hager | Excel For You

  3. Pingback: #Excel Super Links #128 – shared by David Hager | Excel For You

  4. Pingback: #Excel Super Links #150 – Special Edition | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s