# Solved How to seperate mobiles phone numebrs and landlines numebrs

November 14, 2015 at 05:26:01
Specs: Macintosh
 HI i have more than 10,000 customer uk phone numbers in one excel sheet column A. spent no. of hours to create database from bills and phone lists. i need only mobile phone numbers.Examplenumbers starting with: 07 are mobile numbers and rest are landlines. both are 11 digits long. how do i filter only mobile phone numbers.COLUMN A079656654640785667589902086765654079876556780765434567602076786546011247657580178987667807511453765Please help.i am trying to use option FILTER ,when when i click there it says FILTER NUMBERS and then i clicked CUSTOM FILTER,,, and then BEGIN WITH 07,,, still it not showing anything.i am using WINDOWS MICROSOFT OFFICE 2010message edited by sulmanmushtaq

See More: How to seperate mobiles phone numebrs and landlines numebrs

November 14, 2015 at 18:26:24
 Actually, if they all start with 0, then Option 1 should be enough since as far as Excel is concerned, the leading 0 does not really exist. Excel should see each number as a 10 digit number "internally" and therefore the LEFT function will see the second displayed digit as the first.Give it a try.

#1
November 14, 2015 at 05:47:26
 Using a Helper column would seem the simplest option.With your data like:``` A 1) 07965665464 2) 07856675899 3) 02086765654 4) 07987655678 5) 07654345676 6) 02076786546 7) 01124765758 8) 01789876678 9) 07511453765 ```In cell B1 enter the formula: =IF(LEFT(A1,2)="07",A1,"")Drag down as many cell as needed.Since your phone numbers are saved as TEXT, as indicted by the leading Zero,the 07 in the formula must be enclosed in quote marks.Also, you must do a Copy / Paste / Values on column B to convertthe formulas into real numbers.See how that works for you.MIKEmessage edited by mmcconaghy

Report •

#2
November 14, 2015 at 06:26:05
 Thank you for your respond.sorry what value do i have to put between inverted commas at the end of formula ?=IF(LEFT(A1,2)="07",A1,"")bear in mind all cell format are CUSTOMthen i type 11 zero , like 00000000000 ,to add zero before numbers,,,

Report •

#3
November 14, 2015 at 06:41:40
 OK, did not know you had a Custom Number, but you will have to convert them to TEXT if you want to keep the leading Zero.Even with your Custom Format, Excel displays the zerobut it you look at the address bar, you will see that the zero is not there.Having it as TEXT will keep the leading zero.MIKEmessage edited by mmcconaghy

Report •

Related Solutions

#4
November 14, 2015 at 06:47:05
 i have tried.still no luck.

Report •

#5
November 14, 2015 at 06:48:11

Report •

#6
November 14, 2015 at 06:54:42
 OkLet me try again.Thanx

Report •

#7
November 14, 2015 at 06:58:29
 NaIt displays BLANK CELLS with no values :(

Report •

#8
November 14, 2015 at 07:01:32
 What did you do to convert the numbers?In the address bar above the column letters, does it show with the zero or without the zero?If it shows without the zero, it is a number and you will have to re-enter the number as TEXT.MIKEhttp://www.skeptic.com/

Report •

#9
November 14, 2015 at 07:05:28
 You must format column A as TEXT before you enter any data.If you simply change the format to TEXT with data already in place, it won't work.MIKEhttp://www.skeptic.com/

Report •

#10
November 14, 2015 at 07:10:51
 Yes i have formatted the column A as TEXT.then Paste data.on cells leading zero appearson address bar zero not showingSill no working.

Report •

#11
November 14, 2015 at 07:11:17
 I'm going away for the Weekend and will not be back until Sunday night,so I will not be able to answer any questions till then.MIKEhttp://www.skeptic.com/

Report •

#12
November 14, 2015 at 07:13:14
 Thank you very much MIKE for trying to helpI really appreciate. i will try again lator .

Report •

#13
November 14, 2015 at 07:13:32
 Start a new sheet,Format Column A as TextCopy the numbers you posted in the forumand Paste them into column AUse the Paste Special / TextSee how that works.Have to go now.MIKEhttp://www.skeptic.com/

Report •

#14
November 14, 2015 at 16:48:53
 You may need to explain to us why you have used a Custom Format of 11 digits if every number has a "fake" leading 0. If neither of the options offered below work for your data, then I think we will need that explanation.Note: For these solutions, you do not have to format the column as Text. You can use numbers and your 11 zero Custom Format.The example data that you posted shows a leading zero for all numbers. I don't know if this is true for all 10K of your numbers, so I am going to offer 2 options. The first option assumes that the actual phone number is only the last 10 digits. The second option assumes that there are cases where the phone number really is 11 digits.Option 1 (10 digit phone numbers, all have a fake leading zero):Try this in against your Custom Formatted list of numbers:=IF(LEFT(A1,1)="7",A1,"")Explanation:Because of your Custom Formatting, the leading 0 is not really there, therefore the first digit of the phone number is actually the second digit that appears in the cell. That formula will extract all numbers that have the "fake" leading zero and a 7 as the second digit showing in the cell. Since we are using the LEFT function - which is a Text function - we have to compare the 7 in the number against a Text 7, therefore we enclose the 7 in the formula in quotes. Excel takes care of the conversion internally.Option 2 (Some 10 digit numbers and some 11 digit numbers):=IF(AND(LEFT(A1,1)="7",LEN(A1)=10),A1,"")Explanation:If you happen to have landline numbers that are actually 11 digits and any of them start with a 7, then the formula offered in Option 1 will extract those numbers because LEFT(A1,1) will equal "7". However, you have told us that cell phone numbers start with 07. This formula will ignore all 11 digit phone numbers, even if they start with a 7, by checking to see if the actual number is really only 10 digits.e.g. Note Row 4. Even though LEFT(A4,1)="7" is TRUE, LEN(A4)=10 is FALSE. Therefore the AND is FALSE, therefore the IF is FALSE and the number is not extracted.``` A B 1) 07965665464 07965665464 2) 02856675899 3) 07086765654 07086765654 4) 77987655678 5) 07654345676 07654345676 9) 03511453765```Let us know if either of those options work for you.

Report •

#15
November 14, 2015 at 18:01:16
 Thank you for your respond, i am going to try this at morning as it's midnight in Ukall 10k numbers are 11 digits inc mobile and landlines and all starting with 0. when i copied from different files and sheets and pasted to excel , leading zero was disappeared and i actually needed the zero....then I googled how to add leading zero and found custom format option....I believe your option 2 will work , i will keep you posted at morning,,,,I appriciate your time and detailed explanation

Report •

#16
November 14, 2015 at 18:26:24
 Actually, if they all start with 0, then Option 1 should be enough since as far as Excel is concerned, the leading 0 does not really exist. Excel should see each number as a 10 digit number "internally" and therefore the LEFT function will see the second displayed digit as the first.Give it a try.

Report •

#17
November 16, 2015 at 02:23:29
 wao great...Option 1 Worked.Thank you Excel Genius.:)

Report •

#18
November 16, 2015 at 06:05:16