# Categorizing phone numbers according to pattern

#### hasanlianar

##### New Member
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

<tbody>
</tbody>

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Special-K99

##### Well-known Member
So ALL phone numbers are 7 digits in length yes?

Referring to your description of the problem:

And 2342222 matches with ABCAAAA
So the first digit in the numbers gets given the letter A, any new digit gets given the next letter in the alphabet, ie B then C.
Since we already have 2 as the first digit the remaining digits also being 2 are given the existing letter A to result in ABCAAA.
Is that correct?

So we could never have BCDBBB could we? Or CDECCC? Since we ALWAYS since the first digit will always be the letter A.
Is that correct?

Now referring to your list of numbers they seem to follow a different pattern what you've described earlier.

I'm wondering what the point of all this is?

Last edited:

#### hasanlianar

##### New Member
All phone numbers are 7 digits.
First letter is always A. The rest can vary. The point here is that numbers matching different patters are sold for different prices. For example, A000000 (3000000, 8000000,...) is sold for higher price than A00000A (3000003, 8000008, ... ), since demand for A000000 is higher. I need to find to which patters a given number belongs to, so that I can later find its price.

#### StephenCrump

##### MrExcel MVP
hasanlianar, it sounds like a simple VLOOKUP will do what you want.

B11: =VLOOKUP(A11,A2:B8,2,FALSE)

Or am I missing something?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">phone numbers</td><td style="font-weight: bold;text-align: center;color: #333333;;">patterns</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">2000000</td><td style="text-align: center;color: #333333;;">AAAAAAA</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">2000001</td><td style="text-align: center;color: #333333;;">A000000</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">2000002</td><td style="text-align: center;color: #333333;;">A00000A</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">2000022</td><td style="text-align: center;color: #333333;;">A00000B</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">2000033</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">AA00000</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;color: #333333;;">2002222</td><td style="text-align: center;color: #333333;;">AB00000</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;color: #333333;;">2003333</td><td style="text-align: center;color: #333333;;">AAA0000</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="font-weight: bold;background-color: #FFFF00;;">Find this one:</td><td style="text-align: right;background-color: #FFFF00;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;color: #333333;background-color: #FFFF00;;">2000033</td><td style="text-align: center;background-color: #FFFF00;;">AA00000</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

#### inactiveuserps07

##### Banned user

Per original post: "For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA..."

These don't match what I see in the sample data from the original post.

When I look beside 2000000, I see AAAAAAA.

When I look beside 2342222, I see ABABBBB.

???

#### dreid1011

##### Well-known Member
Per original post: "For example, 2000000 matches with A000000 or 2342222 matches with ABCAAAA..."

These don't match what I see in the sample data from the original post.

When I look beside 2000000, I see AAAAAAA.

When I look beside 2342222, I see ABABBBB.

???

The goal is to match the pattern of column A to the pattern in column B. If A2 = 2000000, then the corresponding pattern in column B would be A000000. Now to match that pattern with A1 wherever it is in column B.

Last edited:

#### inactiveuserps07

##### Banned user

@dried1011, I understand the goal. But the poster's original data doesn't match later data. If you look at the list he posted as sample data, it starts this way:

 phone numbers patterns 2000000 AAAAAAA 2000001 A000000 2000002 A00000A 2000022 A00000B

<tbody>
</tbody>

... so 2000000 matches AAAAAAA not A000000 in his original data set. So I'm not seeing the pattern correlation.

#### StephenCrump

##### MrExcel MVP
Or am I missing something?

Oops yes, everything!

With helper cells:

B2: =CHAR(C2)&CHAR(D2)&CHAR(E2)&CHAR(F2)&CHAR(G2)&CHAR(H2)&CHAR(I2)
C2: 65
D2: =IF(MID(\$A2,COLUMNS(\$C2:D2),1)="0",48,IFERROR(INDEX(\$C2:\$I2,FIND(MID(\$A2,COLUMNS(\$C2:D2),1),LEFT(\$A2,COLUMNS(\$C2:C2)))),MAX(\$C2:C2)+1))

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;color: #333333;;">Numbers</td><td style="font-weight: bold;text-align: center;color: #333333;;">Pattern</td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="font-weight: bold;text-align: center;color: #333333;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;color: #333333;;">2000000</td><td style="text-align: center;color: #333333;;">A000000</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;color: #333333;;">2000001</td><td style="text-align: center;color: #333333;;">A00000B</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">48</td><td style="text-align: right;;">66</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;color: #333333;;">2342222</td><td style="text-align: center;color: #333333;;">ABCAAAA</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">66</td><td style="text-align: right;;">67</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td><td style="text-align: right;;">65</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;color: #333333;;">2340436</td><td style="text-align: center;color: #333333;;">ABC0CBD</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">66</td><td style="text-align: right;;">67</td><td style="text-align: right;;">48</td><td style="text-align: right;;">67</td><td style="text-align: right;;">66</td><td style="text-align: right;;">68</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;color: #333333;;">2030432</td><td style="text-align: center;color: #333333;;">A0B0CBA</td><td style="text-align: center;color: #333333;;">65</td><td style="text-align: right;;">48</td><td style="text-align: right;;">66</td><td style="text-align: right;;">48</td><td style="text-align: right;;">67</td><td style="text-align: right;;">66</td><td style="text-align: right;;">65</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

#### inactiveuserps07

##### Banned user
Wait, Hasan ... I think I'm finally understanding what you're trying to do.

Could you please decode the following:

2000001

4010009

Would they be ...?:

A000001

C01000H

If not, please correct and explain. Then I'll be able to help you better. (It's all coming together in my mind ...)

Last edited:

#### inactiveuserps07

##### Banned user
If I'm understanding you correctly, then a formula for "translating" any phone number would be as follows assuming phone number is in A2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H")

This can be used to match any phone number against a list. For instance, if your price-per-pattern is listed in a table, you could use the above with INDEX-MATCH to find the price that corresponds with the pattern, having only entered the phone number.

So let's say that we formatted the phone number list as a table called Table1 in Column A. And we formatted the unsorted patterns list as a table called Table2 in Column B. We could then place the following formula into cell D2:

=IFERROR("Table 2 Row "&MATCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H"),Table2,0),"---NoMatch---")

... and copy-drag it down as far as we want.

This would tell us where to find each number from Table1 in Table2.

Or, if you just wanted a conversion machine, someone could enter any number in A2 and have B2 create the conversion pattern by placing the first formula above in B2:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"2","A"),"3","B"),"4","C"),"5","D"),"6","E"),"7","F"),"8","G"),"9","H")

Last edited:

1,141,715
Messages
5,708,052
Members
421,541
Latest member
Akidev

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back