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

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

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

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