Replacing characters

[Guest]

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

 

[Roger Govier]

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!!!!

 

[R.VENKATARAMAN]

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

 

[Aladin Akyurek]

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

 

[R.VENKATARAMAN]

I wonder whether it will change 2432 as 2402

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

 

[Guest]

Thanks for the info.

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!!!!

 

[Guest]

Thanks mate. I tried this and it worked.

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

 

[Guest]

Thanks for the info.

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

 

[Bob Phillips]

It will, which is where it fails.

--

HTH

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

 

[Myrna Larson]

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(A1>2999,A1-3000,A1)

 

[Aladin Akyurek]

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

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