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.

Example
numbers starting with: 07 are mobile numbers and rest are landlines. both are 11 digits long. how do i filter only mobile phone numbers.

COLUMN A

07965665464
07856675899
02086765654
07987655678
07654345676
02076786546
01124765758
01789876678
07511453765

Please 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 2010

message edited by sulmanmushtaq


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

Report •


✔ Best Answer
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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#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 convert
the formulas into real numbers.

See how that works for you.

MIKE

http://www.skeptic.com/

message 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 CUSTOM
then 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 zero
but 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.


MIKE

http://www.skeptic.com/

message 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
I was updating my reply, see my edited reply #3

MIKE

http://www.skeptic.com/


Report •

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

Report •

#7
November 14, 2015 at 06:58:29
Na
It 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.

MIKE

http://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.

MIKE

http://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 appears
on address bar zero not showing

Sill 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.

MIKE

http://www.skeptic.com/


Report •

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

Report •

#13
November 14, 2015 at 07:13:32
Start a new sheet,
Format Column A as Text
Copy the numbers you posted in the forum
and Paste them into column A
Use the Paste Special / Text

See how that works.

Have to go now.

MIKE

http://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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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 Uk

all 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
✔ Best Answer
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.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
I'm glad I could help. If you don't mind, please mark the thread as solved by choosing a Best Answer.

Thank you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question