Mobile Phone Numbers/ Cell Phones

**pdiminski** · 02-10-2010, 11:29 AM

I am desperate for some help on this one. The problem is now accepting numbers starting with zero but it does involve formatting/ masks.

I have a sheet with many mobile/ cell phone numbers:
07771123456 for example
But I need to change this format to drop the "0" and add the country code "44", so need to automate this somehow with a mask/macro.

07771123456 needs to change to 447771123456 and there is thousand of these so I cant face any more hours of doing it manually, there is a way isnt there? please for my sanity

I use office 2007.

**martindwilson** · 02-10-2010, 11:58 AM

if they are all the same length use in another column
=44&RIGHT(A1,10)
drag down then copy column paste back special values
if different lengths then
=44&MID(A1,2,20)
should cover it

**khamilton** · 02-10-2010, 12:04 PM

you could use substitute -->=SUBSTITUTE(A1,0,44) <-- A1 is the old 07771123456 would change it to 44777123456. Is that something you could work with?

**DonkeyOte** · 02-10-2010, 12:27 PM

kh, the SUBSTITUTE would cause issues if a 0 occurs elsewhere

Another variation on the theme might be:

B1: =REPLACE(A1,1,1,44)
assumes left most char is always a 0

**khamilton** · 02-10-2010, 12:30 PM

ahh! very true thanks for pointing that out.

**DonkeyOte** · 02-10-2010, 12:31 PM

You could just add a 1 to the SUBSTITUTE as optional 4th parameter though...

**khamilton** · 02-10-2010, 12:35 PM

yes I just tested it like so--> =SUBSTITUTE(A1,0,44,1)

**Vitaly K.** · 02-13-2010, 04:22 PM

SUBSTITUTE is an appropriate approach.

but you can also use the command "Text to Columns..." in menu Data

if you choose "fixed width" option, you can separate the first "0" from the rest of the digits.

then you can join them with "44" with operator "&"

probably in this case this is not the simplest way, but sometimes for some reasons you can't use formulas, and then this method will help

**pdiminski** · 02-16-2010, 01:55 PM

Perfect many thanks!! legend.

**pdiminski** · 02-16-2010, 01:56 PM

I went down the =SUBSTITUTE(A1,0,44,1) route which worked a dream. Thanks for all your suggestions. There replace functin as mentioned sometimes removed all the "0's".

Many thanks all.

**DonkeyOte** · 02-16-2010, 02:06 PM

I'm glad you've resolved but just to clarify:

Originally Posted by pdiminski

There replace functin as mentioned sometimes removed all the "0's"

The REPLACE Worksheet Function as illustrated would only ever replace the first character of the number... if you were running the Edit -> Replace Tool (via Menus) then yes that would replace all 0's.