Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete unwanted records
I have data files (12 digits numbers) in ascending order under column A and I
need to keep data that only fall under certain numbers in position 2 to 4, (eg. 453 for Column A1,A2,A3 and 560 for Column A6), the rest will be deleted off. Thanks. A 1 245301020915 2 245301020931 3 245301020952 4 245701020959 5 249651010081 6 256051017001 7 256051017002 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete unwanted records
Hi,
Use the following formula in cell B1 and copy it down =OR(MID(A1,2,3)="453",MID(A1,2,3)="560") Sort the two columns on column B. Highlight all the rows that say FALSE and delete them. If this helps, click the Yes button. -- Thanks, Shane Devenshire "TPG" wrote: I have data files (12 digits numbers) in ascending order under column A and I need to keep data that only fall under certain numbers in position 2 to 4, (eg. 453 for Column A1,A2,A3 and 560 for Column A6), the rest will be deleted off. Thanks. A 1 245301020915 2 245301020931 3 245301020952 4 245701020959 5 249651010081 6 256051017001 7 256051017002 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete unwanted records
If I need the data to be covered from range "453" + "457" + "460" + "477" +
"496" + say up to "560", then the whole formula will look very long. Any shorter formula? Please help. Thank you. A 242201020915 243401320931 245303020952 245707028340 246001620959 247701720901 249656310085 256057813001 268009017062 276351015601 "ShaneDevenshire" wrote: Hi, Use the following formula in cell B1 and copy it down =OR(MID(A1,2,3)="453",MID(A1,2,3)="560") Sort the two columns on column B. Highlight all the rows that say FALSE and delete them. If this helps, click the Yes button. -- Thanks, Shane Devenshire "TPG" wrote: I have data files (12 digits numbers) in ascending order under column A and I need to keep data that only fall under certain numbers in position 2 to 4, (eg. 453 for Column A1,A2,A3 and 560 for Column A6), the rest will be deleted off. Thanks. A 1 245301020915 2 245301020931 3 245301020952 4 245701020959 5 249651010081 6 256051017001 7 256051017002 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete unwanted records
Suppose you listed those numbers 453, 457, 460, 477, 496 in cells M1:M5 (as
numbers). Then fill 496 down to 560 in as many cells as it needs. Then you could try this in B1: =IF(ISNA(MATCH(MID(A1,2,3)*1,M:M,0)),"remove","ok" ) Copy this down as required, then you could use autofilter to identify the ones you need to delete. Hope this helps. Pete "TPG" wrote in message ... If I need the data to be covered from range "453" + "457" + "460" + "477" + "496" + say up to "560", then the whole formula will look very long. Any shorter formula? Please help. Thank you. A 242201020915 243401320931 245303020952 245707028340 246001620959 247701720901 249656310085 256057813001 268009017062 276351015601 "ShaneDevenshire" wrote: Hi, Use the following formula in cell B1 and copy it down =OR(MID(A1,2,3)="453",MID(A1,2,3)="560") Sort the two columns on column B. Highlight all the rows that say FALSE and delete them. If this helps, click the Yes button. -- Thanks, Shane Devenshire "TPG" wrote: I have data files (12 digits numbers) in ascending order under column A and I need to keep data that only fall under certain numbers in position 2 to 4, (eg. 453 for Column A1,A2,A3 and 560 for Column A6), the rest will be deleted off. Thanks. A 1 245301020915 2 245301020931 3 245301020952 4 245701020959 5 249651010081 6 256051017001 7 256051017002 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete unwanted sheet | Excel Discussion (Misc queries) | |||
Delete unwanted data | Excel Worksheet Functions | |||
macro to delete unwanted data | Excel Discussion (Misc queries) | |||
Delete unwanted page breaks. | Excel Worksheet Functions | |||
Delete Unwanted Data | Excel Discussion (Misc queries) |