Excel: Categorizing phone numbers according to pattern


Hello, I have a list of phone numbers and patterns as shown below. Is it possible to somehow match phone numbers with patters. For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA. I only can think of nested IF formulas, though that formula will be too long and complicated. Could someone help with a better idea? Please note that there can be even more numbers, for example, instead of 2000000, there can be 3000000

phone numbers patterns
2000000 AAAAAAA
2000001 A000000
2000002 A00000A
2000022 A00000B
2000033 AA00000
2002222 AB00000
2003333 AAA0000
2020000 A0A0000
2020002 A0A0A0A
2020202 A0AAAAA
2022222 ABABABA
2023333 ABAAAAA
2030000 ABBBBBB
2030303 AAAAAA0
2032222 AAAAAAB
2033333 AA0A0A0
2034444 AAB0000
2100000 AABBBBB
2200000 AABABAB
2200002 A0B0000
2200003 A0B0B0B
2200022 A0BBBBB
2200033 AB0B0B0
2202020 ABA0000
2202222 ABB0000
2203333 ABBBBBA
2220000 ABC0000
2220002 ABCBCBC
2222000 ABCCCCC
2222200 AAA000A
2222220 AAABBBB
2222222 AAAA000
2222223 AAAABBB
2222233 AAAAA00
2222333 AAAAABB
2223333 A0000AA
2230000 A0000BB
2232222 A00AAAA
2232323 A00BBBB
2233333 AA0000A
2234444 AA0000B
2300002 AA000AA
2300003 AA000BB
2300004 AA0AAAA
2302222 AA0BBBB
2303030 AABAAAA
2303333 AABCCCC
2304444 A0BAAAA
2320000 A0BCCCC
2320002 A0A000A
2322222 A0ABBBB
2323232 AB0000A
2323333 AB0000B
2324444 AB0000C
2330000 AB0AAAA
2330003 AB0BBBB
2332222 AB0CCCC
2333332 ABCAAAA
2333333 ABCBBBB
2334444 ABCDDDD
2340000 ABA000A
2342222 ABABBBB
2343333 ABACCCC
2343434 ABB000B
2344444 ABBAAAA
2345555 ABBCCCC


This question generated 13 answers. To proceed to the answers, click here.

This thread is current as of June 08, 2017.


For more resources for Microsoft Excel