noob stuck with automatically formatting Telephone numbers - REWARD FOR THE HELPER :D?

Hi guys,

I've become unstuck with a little problem involving Phone numbers of customers.
I have recently purchased some data from a data gathering company and its slightly different to the data I normally use. I'm trying to format it so that it is the same at my normal data.

problem:

Normal Data - 01626 439132
New Data - 01626 439 132

so old format was five characters, space, six characters.
New format is five characters, space, three characters, space, three characters.

My whole database has duplicates removed via a search for matching Phone numbers (as address and contact name is too unreliable) so you can see this is a big problem for me as i cant use the purchased data until i fix this issue

Please find Attached 2 samples of data showing my problem in my worksheets

any help is MUCH appreciated and I would gladly hire this bloke to rub food over himself saying your the best excel helper!
https://www.fiverr.com/beautifulbaco...50408912432180

broken data.xlsxfixed data.xlsx

You can use this formula in, say, H2, then copy down:

=LEFT(SUBSTITUTE(F2," ",""),5)&" "&RIGHT(SUBSTITUTE(F2," ",""),LEN(SUBSTITUTE(F2," ",""))-5)

Fix the values, then copy/paste over the original values in column F, and then you can delete column H.

Please note that the STD code is not always 5 characters - think of calls to London, Manchester, Birmingham etc.

Hope this helps.

Pete

Hi Pete,
That works a treat mate,
any advice on how i can set it up for the smaller STD codes? would i need a new formula for each sized STD? as I can filter A-Z for areas and work around it that way?
Cheers
Matt

Originally Posted by Jerwood

... any advice on how i can set it up for the smaller STD codes? ...

Well, there are also longer STD codes as well - some of 6 digits and even some of 7 (mainly Northern Ireland and remote areas of Scotland).

I just thought I would point it out to you. Their importance will depend on how your existing database treats these numbers - if they are always 5 digits followed by a space followed by the other numbers, then the variable-length codes will make no difference.

Hope this helps.

Pete