Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
OhhAhh
 
Posts: n/a
Default Replacing characters

I have a sheet with old phone numbers on it. The numbers all have a leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do a
regular find and replace it will replace all 3's within the numbers not just
the leading one. Can someone out there tell me what the best way to achieve
this is.

Thanks

Graeme
  #2   Report Post  
Roger Govier
 
Posts: n/a
Default

Hi Graeme

You say ALL numbers start with 3 and you want ALL numbers to start with 0
instead.
If this is true, and the numbers are in column A then in B1 enter
="0"&MID(A1,2,20) - assuming 20 covers the longest number you have
Copy down column B or double click the fill handle at the bottom right of
the cell.
Copy the whole of column B
Paste Special=Values

I would not Paste over the exisiting data immediately - just in case!!!!

--
Regards
Roger Govier
"OhhAhh" wrote in message
...
I have a sheet with old phone numbers on it. The numbers all have a
leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do
a
regular find and replace it will replace all 3's within the numbers not
just
the leading one. Can someone out there tell me what the best way to
achieve
this is.

Thanks

Graeme



  #3   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

suppose your first tel no. is in B4 use this formula in C4
=IF(LEFT(B4,1)="3","0"&RIGHT(B4,3),B4)
copy this fomula down
replaced number will be in text format and others in number format in C
you can format all C as text if required

OhhAhh wrote in message
...
I have a sheet with old phone numbers on it. The numbers all have a

leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do

a
regular find and replace it will replace all 3's within the numbers not

just
the leading one. Can someone out there tell me what the best way to

achieve
this is.

Thanks

Graeme



  #4   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:
I have a sheet with old phone numbers on it. The numbers all have a leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do a
regular find and replace it will replace all 3's within the numbers not just
the leading one. Can someone out there tell me what the best way to achieve
this is.

Thanks

Graeme

  #5   Report Post  
R.VENKATARAMAN
 
Posts: n/a
Default

I wonder whether it will change 2432 as 2402

Aladin Akyurek wrote in message
...
=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:
I have a sheet with old phone numbers on it. The numbers all have a

leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I

do a
regular find and replace it will replace all 3's within the numbers not

just
the leading one. Can someone out there tell me what the best way to

achieve
this is.

Thanks

Graeme





  #6   Report Post  
OhhAhh
 
Posts: n/a
Default

Thanks for the info.

"Roger Govier" wrote:

Hi Graeme

You say ALL numbers start with 3 and you want ALL numbers to start with 0
instead.
If this is true, and the numbers are in column A then in B1 enter
="0"&MID(A1,2,20) - assuming 20 covers the longest number you have
Copy down column B or double click the fill handle at the bottom right of
the cell.
Copy the whole of column B
Paste Special=Values

I would not Paste over the exisiting data immediately - just in case!!!!

--
Regards
Roger Govier
"OhhAhh" wrote in message
...
I have a sheet with old phone numbers on it. The numbers all have a
leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do
a
regular find and replace it will replace all 3's within the numbers not
just
the leading one. Can someone out there tell me what the best way to
achieve
this is.

Thanks

Graeme




  #7   Report Post  
OhhAhh
 
Posts: n/a
Default

Thanks for the info.

"R.VENKATARAMAN" wrote:

suppose your first tel no. is in B4 use this formula in C4
=IF(LEFT(B4,1)="3","0"&RIGHT(B4,3),B4)
copy this fomula down
replaced number will be in text format and others in number format in C
you can format all C as text if required

OhhAhh wrote in message
...
I have a sheet with old phone numbers on it. The numbers all have a

leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do

a
regular find and replace it will replace all 3's within the numbers not

just
the leading one. Can someone out there tell me what the best way to

achieve
this is.

Thanks

Graeme




  #8   Report Post  
OhhAhh
 
Posts: n/a
Default

Thanks mate. I tried this and it worked.

"Aladin Akyurek" wrote:

=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:
I have a sheet with old phone numbers on it. The numbers all have a leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do a
regular find and replace it will replace all 3's within the numbers not just
the leading one. Can someone out there tell me what the best way to achieve
this is.

Thanks

Graeme


  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default

It will, which is where it fails.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R.VENKATARAMAN" wrote in message
...
I wonder whether it will change 2432 as 2402

Aladin Akyurek wrote in message
...
=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:
I have a sheet with old phone numbers on it. The numbers all have a

leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if

I
do a
regular find and replace it will replace all 3's within the numbers

not
just
the leading one. Can someone out there tell me what the best way to

achieve
this is.

Thanks

Graeme





  #10   Report Post  
Myrna Larson
 
Posts: n/a
Default

But it ONLY works if the first digit is 3. If it isn't, and 3 occurs later in
the number, it will fail. Another approach if the data is numeric:

=IF(A12999,A1-3000,A1)


On Thu, 3 Mar 2005 06:19:05 -0800, OhhAhh
wrote:

Thanks mate. I tried this and it worked.

"Aladin Akyurek" wrote:

=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:
I have a sheet with old phone numbers on it. The numbers all have a

leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I

do a
regular find and replace it will replace all 3's within the numbers not

just
the leading one. Can someone out there tell me what the best way to

achieve
this is.

Thanks

Graeme





  #11   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

As noted, that was a wrong proposal. Here another, purported to be
correct...

=SUBSTITUTE(LEFT(A1),"3",0)&REPLACE(A1,1,1,"")

OhhAhh wrote:
Thanks mate. I tried this and it worked.

"Aladin Akyurek" wrote:


=SUBSTITUTE(A1,"3","0",1)

OhhAhh wrote:

I have a sheet with old phone numbers on it. The numbers all have a leading
3 (ie 3476) which I want to replace with a 0 (ie 0476). Of course if I do a
regular find and replace it will replace all 3's within the numbers not just
the leading one. Can someone out there tell me what the best way to achieve
this is.

Thanks

Graeme


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
How do you make some characters in a cell bold and some not? tracman Excel Discussion (Misc queries) 4 March 28th 05 05:17 AM
vlookup first 4 characters only in a column Gus Excel Discussion (Misc queries) 6 February 20th 05 05:41 PM
Characters and Formulas Bonny Excel Discussion (Misc queries) 1 February 11th 05 05:06 PM
how do i filter data by number of characters in excel? monkeytrader Excel Discussion (Misc queries) 2 February 7th 05 06:38 PM
255 characters in a cell Claire Excel Discussion (Misc queries) 5 January 20th 05 11:16 PM


All times are GMT +1. The time now is 11:37 PM.

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"