Filter & remove incorrectly formatted mobile numbers

Hi Excel wizards

I have a sheet with a long list of UK mobile numbers, a fair percentage of which are incorrectly formatted due to people entering them incorrect when registering. I currently use basic filtering to arrange them then manually delete those that are incorrect but it's quite time consuming and I'm sure there must be an easier way but I don't know how to use VBA too well, which I imagine is what's needed to do this automatically and hopefully in one sweep.

What I'd ideally like is a piece of code, or formula, that;

A) Searches the whole column for all numbers starting with either 447 or 7 then;
B) If string starts with 44, does the it contain 12 digits? If string starts with 7, does it contain 10 digits?, if yes then;
C) Add "44" to all 10 digit numbers. then;
D) Delete all other rows from the sheet and leave only rows with correctly formatted numbers (447999999999)

I've attached an example, the raw data being in column A/B and the desired result in column D/E.

PhoneNumbers.xlsx

Please note, the sheet attached is not how it might always be and just provided as an example. The number of rows are in the 100s/1000s and the number of columns can vary between 10 and 30 (these contain varying customer attributes depending on project). I don't want to edit anything else, just remove all rows with incorrect numbers.

I hope that makes sense, and is possible because it'd make my life so much easier. Thanks for your help with this

Please Login or Register  to view this content.

Slightly different approach, I was having problems with the number formatting:

Please Login or Register  to view this content.

Hi. Thanks for the quick response.

I'll give that code a go and let you know, if not then I'll record the macro and get back to you.

Thanks

Hi stnkynts. That's perfect, it works and does exactly what I asked for. However, I've just realised a couple of other cases and would like to add an extra step or two, if possible please?

1) I've noticed that some of the incorrectly formatted ones are almost correct but they're like this "4407999999999". So what I need for this is to identify those cells beginning with 4407, and with a length of 13 and basically remove the 0 leaving "447" and a length of 12 like the rest.

2) Some contain spaces (1 or 2) but are correct otherwise, with a mixture of "4407" and "447" prefixes like the case above and the original request. Is there a way of identifying these and remove the spaces? Then for the 4407 ones to remove the 0 as above? Assuming again that what I'm left with is a 12 digit number that starts with "447", any other length should delete the row.

Examples for case 2: "4407999 999999", "4407999 999 999" & "447999 999999", "447999 999 999"

If that can be done then you've absolutely nailed it for me! This is greatly appreciated, thank you.

Please Login or Register  to view this content.

Lovely, that seems to do the trick. This will save me loads of time and a big headache at the end of it

Thank you stnkynts.