• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How do I use Custom AutoFilter to Show All Numbers Ending in 1?

Brandy_M

New Member
I have a table in excel with a customer list in column A. I have tried using the custom autofilter to show any customer number ending in 1, but nothing is working. I have tried formatting the column to both numbers or text and even though I can see account numbers ending in 1, nothing is showing when I use this filter.

How do I format this correctly to work? Thank you in advance for your help.

Brandy
 
Brandy

Firstly, Welcome to the Chandoo.org Forums

That's an interesting question?
As it doesn't appear simply to be able to be done as a Filter

You could add another column add add a formula to your table like: =RIGHT(B3,1)="1"
the filter on the true values in that column
 
Alternatively

Select the drop down next to the Field name for the Column of data you are looking at filtering
Under the Numbers Filter there is a Search Box
Type *1
Apply
 
Without using VBA the only option is to have a helper column, either one that turns your number into an alphanumeric string for example in the cell next to the customer number (assuming data starts in A2) have a formula of ="A"&A2 and then filter on that column using the custom filter option of ends with and 1, or, use =if(right(A2,1)=1,"Y","N") and filter that for the Y(es) and N(o)s

Unfortunately excel custom filters won't treat numbers on their own as text and so ends with, begins with etc can't be used as filters with numeric data only
 
Ha! Great minds think alike. I had already thought of the Right formula solution and used this in the meantime. I will try your other suggestions. Thanks. This is my first time posting on Chandoo and I appreciate the quick response!

Brandy
 
Hi there is a trick for this.
I have used it for Benford Analysis.

1. First copy the cells in adjacent columns (optional to ensure orignal data is entact)
2. Select those copied cells and do text to columns
3. Under last window in Text to columns select "Text"option

Now your numbers will get converted to text and you can apply text filters now.

Filter is different for Numbers and text. I hope this solves your purpose.
 
Just an easy way..
1. Apply filters.
2. select the option "Ends with" from the drop down menu of the filters
3. Mention the requirement(in this example it should be 1)
4. Hit OK.

Hope this helps....:)
 
@Abhi you are correct. My assumption was that Customer column had only numbers and do not have alpha numberic code which excel considers as text. I assumed this because custom filter was not working.

Hence I suggested to first convert that into text with text to columns and then use ends with.

However, as hui suggested we can use *1 which will work even if cell containing numbers.
 
Back
Top