Struggling to use Vlookup and Left formula with telephone numbers

Hello guys,

I have a list of UK mobile phone numbers which all start with 07. I have another list of UK mobile operator codes (e.g 07495 numbers are the mobile operator EE).

I used a Left formula (=Left(a1), 5) to extract the first 5 numbers of my list of numbers and then did a Vlookup to assign the carrier name to each operators dial code in a separate column. However, I got quite a few "N/A"s. I then noticed that some of the carriers have 6 number dial codes (e.g. 074482 )

Is there a way of saying if this mobile numbers starts with the either the 5 or 6 numbers in my list, then it is X operator please?

Thanks for any advice offered.

Can you upload a sample of your data?

If it has a six figure code does this give a 12 digit mobile number?

Windy

Thanks Windy.

prefix mobile network
07107 O2
07400 Three
07401 Three
07402 Three
07403 Three
07404 Lycamobile
07405 Lycamobile
074060 Cheers International
074061 Cheers International
074062 Cheers International
074063 Vortex Telecom
074064 Titanium
074065 Telecom2
074066 24 Seven Communications
074067 TG Support
074068 08Direct
074069 CardBoardFish
07407 Vodafone
07408 Truphone
07409 Orange
07410 Orange
07411 Three
07412 Three
07413 Three
07414 Three
07415 EE / T-Mobile
07416 Orange
074170 Three
074171 CardBoardFish
074172 UPA Telecom
074173 Lycamobile
074174 Lycamobile

As you can see, some are 5 numbers, some are six. As you can see from the sample below, the first 5 numbers aren't necessarily assigned to 1 operator, its the 6th number that defines who the operator is in some cases.

074061 Cheers International
074062 Cheers International
074063 Vortex Telecom
074064 Titanium

All mobile numbers are 11 numbers long, regardless of the length of the operator code p

You can get a list of all the codes for 07 phone numbers from the BT Pricing website, here:

http://www.bt.com/pricing/current/Ca....htm#1631-d0e5

and you can get a table of what the Type codes stand for here:

http://www.bt.com/pricing/current/Ca....htm#0016-d0e5

When I did this several years ago I sorted the lookup table by length of the phone number, so all the 4-digit codes were together, then the 5-digit codes etc., and used a formula that progressively looked at the first 4, then first 5 digits (up to 10), and took the match with the largest number of digits. It will take me some time to dig out the files, if you are interested.

Hope this helps.

Pete

I developed a telephone analysis system which took any phone number and categorised it into the appropriate call type, i.e. Local, National, Mobile, International, International-mobile, Freephone, Directory Enquiries etc., and a Type_2 column gave further details for Mobile (operator) and International (country), so it was more sophisticated than just analysing Mobile calls and the number of digits in the phone number could vary from 3 digits (eg 999) up to 15 for some countries.

Pete

I've stripped out all the other stuff in the attached file, and set it up for 07 numbers only. In the Calls sheet I have put some mobile numbers in column A, and you can use this formula in B2:

=INDEX(Types,MATCH(LEFT(A2,MAX(IF(ISERROR(MATCH(LEFT(A2,4),L_4,0)),0,4),IF(ISERROR(MATCH(LEFT(A2,5),L_5,0)),0,5),IF(ISERROR(MATCH(LEFT(A2,6),L_6,0)),0,6),IF(ISERROR(MATCH(LEFT(A2,7),L_7,0)),0,7))),Num,0))

which can be copied down for as much data as you have in column A. The Destinations sheet contains the lookup table(s), with the part number in column A and the Operator or Type in column B. Note that I have sorted this data by length of code, and by the code itself, and that I have set up named ranges L_4 to L_7 to cover each length.

In the red part of the formula, the first 4 digits are matched with the named range L_4 and returns 0 or 4, then in the blue part the first 5 digits are matched with the range L_5 and returns 0 or 5, and similarly with the green part (6) and the un-coloured part (7), and the formula then takes the largest of these and uses that number of digits in another LEFT function which then matches on the full range of numbers (Num), and returns the type. So, the formula will always take the largest number of digits to match on that it can, which enables you to take a range of numbers and reduce the entries needed in the lookup table.

Note that the data in the table is quite old, based on a download from March 2009, so you will need to use more recent data (and to keep it up-to-date).

Hope this helps.

Pete

a little shorter...
B2 =LOOKUP(2,1/SEARCH(Num,A2),Types)

Sorry, corrected formula is: =LOOKUP(2,1/COUNTIF(A2,Num&"*"),Types)

Okay, Pete, yours and Phuocams ideas both worked fine, thank you both. I can use this for another project also. For this one however, I chose to set up some VB code to auto
1) add a Left(5) and Left(6) formula in new columns to extract the first 5 and 6 digits
2) then did a vlookup of those 2 columns against my operator list
3) then in a 3rd column said "If this is N/a" Then "That" Else "this".

I created it in a macro so it works well, but perhaps a little around the block!

Thanks again.