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

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

✔ Best Answer

Actually, if they allstart 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 seconddisplayeddigit as the first.Give it a try.

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

Using a Helpercolumn 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) 07511453765In 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

message edited by mmcconaghy

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,,,

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

displaysthe 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

message edited by mmcconaghy

i have tried.

still no luck.

Ok

Let me try again.

Thanx

Na

It displays BLANK CELLS with no values :(

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

You must format column A as TEXT beforeyou enter any data.If you simply change the format to TEXT with data already in place, it won't work.

MIKE

Yes i have formatted the column A as TEXT.

then Paste data.on cells leading zero appears

on address bar zero not showingSill no working.

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

Thank you very much MIKE for trying to help

I really appreciate.

i will try again lator .

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 / TextSee how that works.

Have to go now.

MIKE

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

numbersand 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) 03511453765Let us know if either of those options work for you.

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

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

Actually, if they allstart 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 seconddisplayeddigit as the first.Give it a try.

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

wao great...

Option 1 Worked.Thank you Excel Genius.

:)

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.

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History