How do extract numbers with a pattern from a list of numbers ?

**kcolraw** · 09-30-2012, 04:45 AM

Hello,

This is something i can't quite figure out how to do. I work in Telecom marketing, and one of the issues we have to deal with is sorting out numbers into categories, for example 50010000 is a golden category while 50018459 is normal , each will sell for a different price. now i have a range of numbers ( from 911-0000 to 911-9999 )and i want to identify the numbers that are golden category according to these rules:

1- Numbers four Identical Numbers example 911-1122
2- Numbers end with four Identical Numbers example 911-2222
3- Numbers with four sequence Numbers example 911-4567
4- Numbers with three sequence Numbers example 911-0567
5- group of Two similar numbers example 911- 2323

so all numbers should be in one column and the golden ones in another

hope you can help in this

**Ace_XL** · 09-30-2012, 07:25 AM

Perhaps with a formula

If A1 has your number listed use

Please Login or Register  to view this content.

**kcolraw** · 10-01-2012, 01:48 AM

thank you works fine

can you explained the logic behind it ?

**Ace_XL** · 10-01-2012, 04:04 AM

I've tried to break this down..

As per your conditions the formula will return golden if either of these are met..

Please Login or Register  to view this content.

The last 3 or first 3 numbers are consceutive. The formula looks for a match of the Rightmost 3 digits or the leftmost three digits with either {"012","123","234","345","456","567","678","789","890"} and the ISNUMBER returns a TRUE if it finds one. Also, if 4 numbers are consecutive, there would necessarily be 3 consecutive numbers too.

Please Login or Register  to view this content.

If first two digits are same as last two digits e.g. 2525, 9393 etc. and also covers for four consecutive numbers e.g. 1111, 2222 etc.

Please Login or Register  to view this content.

This will check for two sets of two identical numbers. Since all of 00,11,22,33,44,55,66,77,88,99 are all perfectly divisible by 11, the MOD function will return remainder as zero. This covers for 1122, 8833 etc as well as consecutive numbers e.g. 1111,3333,8888 etc.