filtering for numbers ending in 000
 

Hi,

I have been given a task of running a query across 45000 lines of excel to
show only numbers that are rounded to thousands. (i do not want to round
numbers, just report on existing 000's). i have no idea how to do this. i
tried using adavanced auto filter - greater than 999 and ending in 000, but
no results were returned. This is probably simple to do but i have no idea.
(also do not want to bring back numbers with 000 in middle eg 500043, just
ending in 000).
any help would be greatly appreciated,

Thanks, novice user getting frustrated.

Answer: filtering for numbers ending in 000
 

Hi frustrated novice user,

I understand your frustration, but don't worry, I can definitely help you with this task. Here's how you can filter for numbers ending in 000:

1. Select the column that contains the numbers you want to filter.
2. Click on the "Data" tab in the ribbon at the top of the screen.
3. Click on the "Filter" button to turn on filtering for the selected column.
4. Click on the drop-down arrow in the header of the selected column.
5. Click on the "Text Filters" option.
6. Click on the "Ends With..." option.
7. In the "Ends With" dialog box, type
   Code:

   ---

   "000"

   ---

   (without the quotes) and click "OK".

This should filter the column to show only numbers that end in 000. If you want to copy or move these filtered results to another location, you can select the filtered cells and then copy or cut and paste them to a new location.

filtering for numbers ending in 000
 

try:

=IF(RIGHT(A1,3)="000","full thousand","")

and apply autofilter on "full thousand"

filtering for numbers ending in 000
 

Try adding a formula in a helper column and filter on the helper column

So assuming the numbers are in column E, in your helper columne row 2 enter

=MOD(E2,1000)=0

Copy this down, then filter the helper column for TRUE

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bigjackblack999" wrote in
message ...
Hi,

I have been given a task of running a query across 45000 lines of excel to
show only numbers that are rounded to thousands. (i do not want to round
numbers, just report on existing 000's). i have no idea how to do this. i
tried using adavanced auto filter - greater than 999 and ending in 000,
but
no results were returned. This is probably simple to do but i have no
idea.
(also do not want to bring back numbers with 000 in middle eg 500043, just
ending in 000).
any help would be greatly appreciated,

Thanks, novice user getting frustrated.

filtering for numbers ending in 000
 

Hi, this fixed it. thanks very much. (less frustrated novice user)

"Bob Phillips" wrote:

Try adding a formula in a helper column and filter on the helper column

So assuming the numbers are in column E, in your helper columne row 2 enter

=MOD(E2,1000)=0

Copy this down, then filter the helper column for TRUE

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"bigjackblack999" wrote in
message ...
Hi,

I have been given a task of running a query across 45000 lines of excel to
show only numbers that are rounded to thousands. (i do not want to round
numbers, just report on existing 000's). i have no idea how to do this. i
tried using adavanced auto filter - greater than 999 and ending in 000,
but
no results were returned. This is probably simple to do but i have no
idea.
(also do not want to bring back numbers with 000 in middle eg 500043, just
ending in 000).
any help would be greatly appreciated,

Thanks, novice user getting frustrated.