Telephone Number Formatting

[Anima42]

Hi everyone,

I am working with a database of (roughly) 10,000 records and have telephone numbers in a multitude of different formats. I would like a formula which will make them all the same.

There is variation in the numbers, which leads me to believe this will be difficult. Some are missing the initial zero, some have the country code with plus symbol before, some with brackets and the spacing is different across the file.

The closest I've gotten to a solution is with using: [<=9999999] ###-####;0### #### ####

To get the format 0### #### ####, but this won't remove country codes or symbols. Is there a way to force each entry to start with zero (without adding one if it is already there) and to remove country codes/symbols?

Any advice would be greatly appreciated.

[ragulduy]

It would be helpful to get some examples of the different types of formats that you would be working with but I would guess the easiest way would be work from the other end and get the last 11 digits of the number and put a zero in front. Something like:

=REPLACE(REPLACE("0"&RIGHT(SUBSTITUTE(A1," ",""),11),5,0," "),10,0," ")

[Anima42]

It's saying Excel cannot use the number format you typed?

Side note, are you part of Omega Warfare? I recently put together the artwork for a Skat Injector split under the same label.. small world.

[ragulduy]

Sorry, it's a formula not a format. You need to put it in a separate column (like in the attachment). Thinking about it, you may want to extend the formula to:
=REPLACE(REPLACE("0"&RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ",""),"(",""),")",""),11),5,0," "),10,0," ")
so that it can deal with ( and )

I am/run Omega Warfare... The Skat Injector split went to press last week - thanks for the art! I assumed Rick did it... send me an email to omegawarfarerecords [at] gmail [dot] com with your address and I'll drop some copies in the post when they arrived.