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

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