|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:
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 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.
1) 07965665464 07965665464
3) 07086765654 07086765654
5) 07654345676 07654345676
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.