Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 193
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,510
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
find a replace character question jimmc Excel Discussion (Misc queries) 3 January 19th 07 11:47 PM
find a character and replace with a command in Excel Jo Excel Discussion (Misc queries) 1 June 27th 06 12:50 PM
How do you find and replace a Wildcard character in Excel? Wildcard Excel Discussion (Misc queries) 8 August 18th 05 01:18 AM
How to remove or replace a carriage return character in a cell? Patty Excel Discussion (Misc queries) 2 July 26th 05 06:25 PM
Search and replace character with in excell cell JRR Excel Discussion (Misc queries) 1 July 22nd 05 08:30 PM


All times are GMT +1. The time now is 12:49 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"