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.

 

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:

 

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:

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