Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
=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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make some characters in a cell bold and some not? | Excel Discussion (Misc queries) | |||
vlookup first 4 characters only in a column | Excel Discussion (Misc queries) | |||
Characters and Formulas | Excel Discussion (Misc queries) | |||
how do i filter data by number of characters in excel? | Excel Discussion (Misc queries) | |||
255 characters in a cell | Excel Discussion (Misc queries) |