+ Reply to Thread
Results 1 to 7 of 7

Removing duplicate phrases from corresponding columns

  1. #1
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Removing duplicate phrases from corresponding columns

    Hello

    I have a spreadsheet that have 2 columns; Address and City

    The addresses end with the city names attached to it, but I also have a separate column with the city names.

    What I want is to batch remove all the city names that are appended to the addresses in the same cell. I want to have two distinct columns for address and city name.

    Kindly refer to the attached spreadsheet to see what I mean

    Any help with this would be greatly appreciated

    Many thanks
    Attached Files Attached Files
    Last edited by probuddha; 06-20-2016 at 04:01 PM.

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Removing duplicate phrases from corresponding columns

    Try this in C3 and fill down. This will return the value in column A without the value in column B

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Copy column C and Paste values (in place) to get rid of the formulae then copy and paste back into column A.
    Result before copying back to column A


    A
    B
    C
    2
    Address
    City
    3
    11305 Via Balboa Anchorage
    Anchorage
    11305 Via Balboa
    4
    P.O. Box 3652 35670 Kenai Spur Hwy. Ste. 102 Soldotna
    Soldotna
    P.O. Box 3652 35670 Kenai Spur Hwy. Ste. 102
    5
    P.O. Box 2721 Soldotna
    Soldotna
    P.O. Box 2721
    6
    106 US Hwy 31S Bay Minette
    Bay Minette
    106 US Hwy 31S
    7
    P.O. Box 361386 Birmingham
    Birmingham
    P.O. Box 361386
    8
    1703 Center Point Pkwy Center Point
    Center Point
    1703 Center Point Pkwy
    9
    2422 Danville Road Suite C Decatur
    Decatur
    2422 Danville Road Suite C
    10
    2006 Franklin Street Suite 106 Huntsville
    Huntsville
    2006 Franklin Street Suite 106
    11
    2152 Airport Blvd # 106 Mobile
    Mobile
    2152 Airport Blvd # 106
    12
    P.O. Box 8407 Mobile
    Mobile
    P.O. Box 8407
    13
    6316 Piccadilly Square Drive Suite A Mobile
    Mobile
    6316 Piccadilly Square Drive Suite A
    14
    8066 Crossbow Ln. Theodore
    Theodore
    8066 Crossbow Ln.
    15
    1409 Inverness Parkway Tuscaloosa
    Tuscaloosa
    1409 Inverness Parkway
    16
    Tuscany Podiatry 215 Hargrove Rd. E. Tuscaloosa
    Tuscaloosa
    Tuscany Podiatry 215 Hargrove Rd. E.
    17
    Cabot Foot Clinic 3065 Hwy 367
    Cabot
    Cabot
    Cabot Foot Clinic 3065 Hwy 367
    18
    1000 Windover Suite A Jonesboro
    Jonesboro
    1000 Windover Suite A
    19
    Foot & Ankle Center of Little Rock 10020 West Markham St. Little Rock
    Little Rock
    Foot & Ankle Center of Little Rock 10020 West Markham St.
    20
    1117 McLain St. Newport
    Newport
    1117 McLain St.

    Here is your workbook to experiment with:
    Attached Files Attached Files
    Last edited by newdoverman; 06-20-2016 at 03:44 PM.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: Removing duplicate phrases from corresponding columns

    Thank you very much for your prompt response. The formula works perfect.

    However, for some cells like A8 and A17, the address is getting snipped half way or completely (I understand that's because of the position of the city name within the address cell)

    Any way I could make the address cells consider only the last word/s within the cell matching with the city name?

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Removing duplicate phrases from corresponding columns

    I have updated the response. A8 and A17 appear to work correctly. I don't see a problem with A8 as pictured above but I did fix the formula to take care of A17 and all seems OK as far as I can see. Try the download again as I have replaced it with a new version with a new formula.

  5. #5
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: Removing duplicate phrases from corresponding columns

    Thank you very much newdoverman for your help. The formula did just what I wanted.

    Regards

  6. #6
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Removing duplicate phrases from corresponding columns

    Thank you for the feedback.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

  7. #7
    Registered User
    Join Date
    04-13-2013
    Location
    Calcutta
    MS-Off Ver
    Excel 2013
    Posts
    87

    Re: Removing duplicate phrases from corresponding columns

    Thread marked as SOLVED. Thanks again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Removing duplicate rows using only a few columns.
    By HorribleZane13 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-27-2015, 09:34 AM
  2. Highight Duplicate Phrases in a column
    By daneff in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-14-2014, 09:35 AM
  3. [SOLVED] Removing duplicate columns
    By Rec1ne in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-02-2013, 06:04 PM
  4. Removing duplicate in from two columns not in order.
    By bisalk in forum Excel General
    Replies: 5
    Last Post: 10-02-2013, 03:24 AM
  5. Removing Duplicate Values - 2 Columns
    By hpaum99 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 03-27-2010, 12:00 PM
  6. Finding Duplicate Phrases in a File
    By Mis in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2006, 07:40 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1