+ Reply to Thread
Results 1 to 14 of 14

Remove Duplicates in a Cell

  1. #1
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Remove Duplicates in a Cell

    Hi, is there an easy way to remove duplicate text in a cell? For example:

    Cell A1: Ford, Dodge, Ford,
    Answer required is Ford, Dodge,

    Can someone give me a formula to accomplish this, it seems so easy.

    Thanks

  2. #2
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Remove Duplicates in a Cell

    find and replace
    , Ford to blank

  3. #3
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Remove Duplicates in a Cell

    Eh, can you elaborate please?

  4. #4
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,447

    Re: Remove Duplicates in a Cell

    Seems so easy, but rather (I may be wrong) isn't so.

    Would be easy by UDF, but not by built in formulas.
    Best Regards,

    Kaper

  5. #5
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

  6. #6
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Remove Duplicates in a Cell

    Guys in fairness, is this the best you can come up with??

  7. #7
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,359

    Re: Remove Duplicates in a Cell

    who said the best?
    your question = an answer

  8. #8
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Remove Duplicates in a Cell

    Thanks, but I need a formula or a macro to do this correctly. I'll have a look elsewhere, I thought there were excel experts in here.

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,447

    Re: Remove Duplicates in a Cell

    I do not think tim's solution is a solution for this issue. So have a look on short UDF and how it works in attached sample file:

    Please Login or Register  to view this content.
    it is used as any built-in function.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    12-08-2014
    Location
    Texas
    MS-Off Ver
    Pro Plus 2013
    Posts
    2

    Re: Remove Duplicates in a Cell

    Looks pretty expert to me; thanks Kaper. I had no idea one could create a function.

  11. #11
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,447

    Re: Remove Duplicates in a Cell

    Glad to hear that, hope it will suit also brianmcgee.

    As of understanding the function - It's relatively easy to read (almost plain English) one point to notice is that when one adds to a collection (named singles) any item at the item_name key - it will raise an error if there is already such an item in the collection. so after going through all elements in array in a collection there are only single occurences of each item.

  12. #12
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Remove Duplicates in a Cell

    Wow Kaper! That's more like it, you are an expert. Works perfectly thank you. Now how do I set that up in the file I'm working on?

  13. #13
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: 2019 but still use also older versions
    Posts
    7,447

    Re: Remove Duplicates in a Cell

    While in your file
    Alt+F11 Alt+I M
    in open window copy the code
    close the whole VBEditor window (or just activate main excel window) and save your workbook in one of macro-friendly formats (xls, xlsm, xlsb) - not xlsx!
    now you can use this function in this file as any sytandard excel function.

    While re-opening the file later, remember to allow macro excecution.

  14. #14
    Registered User
    Join Date
    03-30-2014
    Location
    Ireland
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Remove Duplicates in a Cell

    Excellent work, thanks Kaper!

+ 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. [SOLVED] Remove duplicates in a single cell
    By SonGoku in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-27-2013, 06:35 AM
  2. Remove Duplicates but keep Cell Spacing
    By emurobot in forum Excel General
    Replies: 4
    Last Post: 01-17-2013, 11:02 AM
  3. Remove duplicates within a cell
    By forzaboro in forum Excel General
    Replies: 16
    Last Post: 08-29-2011, 10:14 AM
  4. Replies: 1
    Last Post: 03-05-2011, 12:37 PM
  5. To remove duplicates and get the cell-address of the original
    By Sagar.Ghole in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-12-2008, 10:30 PM

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