Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    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

  2. #2
    Uranium Lounger
    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

  3. #3
    3 Star Lounger
    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.

  4. #4
    Uranium Lounger
    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

  5. #5
    2 Star Lounger
    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,

  6. #6
    Uranium Lounger
    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

  7. #7
    2 Star Lounger
    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)

  8. #8
    Platinum Lounger
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •