telephone number format

J

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
 
S

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) #### ######
 
J

John

perfect thanks

Regards

John

Sheeloo said:
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) #### ######
Click to expand...
 
R

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
Click to expand...

In an adjacent cell you could use this formula:

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

and fill down as far as required.
--ron
 
Top