Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
|
|||
|
|||
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:
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.
__________________
I am not human. I am an Excel Wizard |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
filtering for numbers ending in 000
try:
=IF(RIGHT(A1,3)="000","full thousand","") and apply autofilter on "full thousand" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom Filter for ending numbers | Excel Discussion (Misc queries) | |||
How do you add three ending '0's to a column of numbers? | Excel Discussion (Misc queries) | |||
Filtering Numbers | Excel Discussion (Misc queries) | |||
Filtering numbers???? | Excel Worksheet Functions | |||
Advanced filtering in Excel to find records ending in xxx? | Excel Discussion (Misc queries) |