Greetings,
I have a great deal of data in col A. I am needing to Filter to only show the items that end in "-xxxxx" where the xxxxx is exactly 5 characters.
the xxxxx could be any character.
Thoughts?
Very Specific Filtering
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
-
- Administrator
- Posts: 78574
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Very Specific Filtering
You can use Advanced Filter for this.
Let's say the data begin in A2.
In an empty column, set up a two-cell criteria range.
Leave the top cell empty.
Enter the formula =MID(A2,LEN(A2)-5,1)="-" in the bottom cell of the two.
It may return #VALUE! but that doesn't matter.
Click in any cell of the data range.
On the Data tab of the ribbon, click Advanced.
Excel will automatically select the data range in the 'List range' box.
Click in the 'Criteria range' box and select the two cells of the criteria range mentioned above.
Click OK to see the result.
Let's say the data begin in A2.
In an empty column, set up a two-cell criteria range.
Leave the top cell empty.
Enter the formula =MID(A2,LEN(A2)-5,1)="-" in the bottom cell of the two.
It may return #VALUE! but that doesn't matter.
Click in any cell of the data range.
On the Data tab of the ribbon, click Advanced.
Excel will automatically select the data range in the 'List range' box.
Click in the 'Criteria range' box and select the two cells of the criteria range mentioned above.
Click OK to see the result.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 43
- Joined: 07 Jun 2023, 15:34
Re: Very Specific Filtering
A workbook with an advanced filter macro
for testing
for testing
-
- 4StarLounger
- Posts: 586
- Joined: 14 Nov 2012, 16:06
Re: Very Specific Filtering
Code: Select all
Sub M_snb()
Columns(1).AutoFilter 1, "*-?????"
End Sub
-
- 4StarLounger
- Posts: 540
- Joined: 30 Mar 2010, 18:49
- Location: United States
Re: Very Specific Filtering
Have bit tried the Sub, but I will. Will test the above attachment soon
-
- 5StarLounger
- Posts: 818
- Joined: 24 Jan 2010, 15:56
Re: Very Specific Filtering
Manually you can do it with autofilter by selecting Text filters - ends with, and entering the -?????
Regards,
Rory
Rory