Find & Replace (2K)

Topic

New Reply

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

Reply | Quote

Replies

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)

Reply | Quote

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.

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

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,

Reply | Quote

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)

Reply | Quote

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).

Reply | Quote