telephone number format

[John]

Hi,
I have a spreadsheet of contacts, over 2000.
The telephone numbers are in the format 09999 999999 or 09999999999

I would like them to be updated to +44(0)9999 999999

How can I accomplish this as a batch update?

Regards

John

 

[Sheeloo]

Can you select the cells having the phone nos?

if yes,
then
1. find and replace the space between numbers with nothing
2. format the cells as CUSTOM with the format
44 (0) #### ######

 

[John]

perfect thanks

Regards

John

Can you select the cells having the phone nos?

if yes,
then
1. find and replace the space between numbers with nothing
2. format the cells as CUSTOM with the format
44 (0) #### ######

 

[Ron Rosenfeld]

Hi,
I have a spreadsheet of contacts, over 2000.
The telephone numbers are in the format 09999 999999 or 09999999999

I would like them to be updated to +44(0)9999 999999

How can I accomplish this as a batch update?

Regards

John

In an adjacent cell you could use this formula:

=TEXT(SUBSTITUTE(A1," ",""),"""+44(""0"")""0000 000000")

and fill down as far as required.
--ron