find replace the 1st character in a cell
 

Hi

I have hundreds of contact numbers that I have imported into XL 07 from MS
BCM07.

The numbers come in with the area code in brackets followed by the number e.g.
(02) 1234 5678

I want the number to look like this example:
0212345678

Of course, the area code differs depending on the contact's location e.g.
(02),(03), (07),(08)

I can do a find & replace edit on the brackets & spaces between the numbers
however when I remove these, the "0" drops off the beginning of the number
e.g.
212345678

Is there a formula, find/replace or other solution?
--
TIA

Pete
WIN XP SP2, XL07, OL & BCM 07, 1 gig ram

find replace the 1st character in a cell
 

(02) 1234 5678

First select the contact numbers...

Find "(" | replace with single quote
Find ")" | replace with blank
Find with single space | replace with no space.

I think this will help u.......







On Mar 31, 8:25*am, Pete wrote:
Hi

I have hundreds of contact numbers that I have imported into XL 07 from MS
BCM07.

The numbers come in with the area code in brackets followed by the number e.g.
(02) 1234 5678

I want the number to look like this example:
0212345678

Of course, the area code differs depending on the contact's location e.g.
(02),(03), (07),(08)

I can do a find & replace edit on the brackets & spaces between the numbers
however when I remove these, the "0" drops off the beginning of the number
e.g.
212345678

Is there a formula, find/replace or other solution?
--
TIA

Pete
WIN XP SP2, XL07, OL & BCM 07, 1 gig ram

find replace the 1st character in a cell
 

Select the column, Select Number formatting then select Custom and in Type
box just enter 10 zeros.

--
Regards,

OssieMac


"Pete" wrote:

Hi

I have hundreds of contact numbers that I have imported into XL 07 from MS
BCM07.

The numbers come in with the area code in brackets followed by the number e.g.
(02) 1234 5678

I want the number to look like this example:
0212345678

Of course, the area code differs depending on the contact's location e.g.
(02),(03), (07),(08)

I can do a find & replace edit on the brackets & spaces between the numbers
however when I remove these, the "0" drops off the beginning of the number
e.g.
212345678

Is there a formula, find/replace or other solution?
--
TIA

Pete
WIN XP SP2, XL07, OL & BCM 07, 1 gig ram