Results 1 to 8 of 8
Thread: Find & Replace (2K)

20030401, 21:45 #1
 Join Date
 Feb 2002
 Location
 Reading, Berkshire, England
 Posts
 199
 Thanks
 9
 Thanked 0 Times in 0 Posts
Find & Replace (2K)
I have an Excel Column that contains telephone numbers, 071 xxx xxxx. I want to change the 071 to 081 with a Find and Replace. The problem is that if the figures 071 appear in the body of the number say 071 071 xxxx, then this second occurrence of the 071 is also changed which I don

20030401, 22:28 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Find & Replace (2K)
You could use this formula in an adjacent column, copy down, then Copy and Paste Special, Values over the original data.
=SUBSTITUTE(A1,"071","081",1)John ... I float in liquid gardens
UTC 7ąDS

20030402, 01:27 #3
 Join Date
 Jun 2001
 Location
 Lawrence, Kansas, USA
 Posts
 202
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find & Replace (2K)
I haven't used substitute before, so I checked it out. When I did it changed both values in the second row. I can't see what's wrong.
Another way to solve the problem would be to use the text to columns in the Data menu. Then change just the area code in that column.

20030402, 04:23 #4
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find & Replace (2K)
If those telephone numbers are in column A, then you could put the formula below in row 1 of an empty column. Then copy it down the column as far as the telephone numbers go. This should replace all of the 071 area codes with 081. Now copy this column and do a Paste Special/Values back over the original column and delete the formulas.
Legare Coleman

20030402, 04:47 #5
 Join Date
 Jan 2001
 Location
 St. George, Maine, USA
 Posts
 158
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find & Replace (2K)
Mike,
The SUBSTITUTE function is not ideal for your case as it will alter the first occurrence of the string "071".
You only would like to alter the first 3 digits if they are "071"
Try using: =IF(LEFT(A1,3)="071","081"&RIGHT(A1,LEN(A1)3),A1)
Copy down and do the copy, paste special/values sequence after checking the results.
Good luck,

20030402, 04:47 #6
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Find & Replace (2K)
You are right, inadequate testing on my part.
=SUBSTITUTE(LEFT(A1,4),"071","081")&RIGHT(A1,9)
corrects my error. If there is no leading space, so that version would be:
=SUBSTITUTE(LEFT(A1,3),"071","081")&RIGHT(A1,9)John ... I float in liquid gardens
UTC 7ąDS

20030402, 19:27 #7
 Join Date
 Jan 2001
 Location
 St. George, Maine, USA
 Posts
 158
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Find & Replace (2K)
Mike,
My first response was made late in the night here.
Now when checking against your actual sample, I noted the the numbers have a leading blank. This alters the formula slightly in order to accommodate for it.
=IF(LEFT(A3,4)=" 071"," 081"&RIGHT(A3,LEN(A3)4),A3)

20030403, 17:10 #8
 Join Date
 Jan 2001
 Posts
 3,788
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Find & Replace (2K)
As the 4th digit of the old style London phone numbers is never 0 and assuming you have no trailing spaces in the phone numbers the following should work.
Instead of finding and replacing 071 look for 071 followed by a space and replace with 0207 followed by a space (or "020 7" if you prefer the alternative format).