Formatting cells for phone numbers

[Stanuk]

Hey, im looking to format a number of cells into a phone number format
I have a rather large excel spreadsheet of around 900 phone numbers
they are currently in different formats such as:

0870 8888888
+44 0800 7777777
+800 (900) 666666
0980 888888

These are obviously just example numbers and not real ones, as you ca
see they are all over the place and in the wrong format that i nee
them in. Changing them all manually would take me forever so ive trie
to create a custom formatting rule to have them represented like this:

+44 (800) 888888

So basically they must all begin with +44 then the next 3 digits mus
be in brackets however the zero must be trimmed from the start, and th
following digits must be on their own, for example:

0800 999999 would turn into +44 (800) 9999999

Now ive managed to change single numbers into this format, for example
055453534 into +44 (554) 53534 using the custom format: +44 (#) howeve
i have had no luck with numbers that are seperated. If anyone could hel
me with this i would be very grateful.

Thanx in advance

 

[Guest]

Hi

Using a helper column and SUBSTITUTE function, you will be able to get rid
of the spaces in your cells:
=SUBSTITUTE(A2," ","")

Hope this helps.
Andy.