Remove a cell containing specific duplicate text "", then pasting results

**Logical999** · 01-16-2018, 04:55 PM

I would like to remove a cell containing specific duplicate text, then pasting results. For example:
exc.png

"closedog" duplicates are removed:
"colton88" duplicates are removed:

etc, for duplicates within ""

I'm using excel 2003 to make it extra complicated

Thank you for your time!!

**queuesef** · 01-16-2018, 10:43 PM

Why don't you A) Use Conditional formatting to highlight the Duplicate cells B) make the data a table, and remove the highlighted duplicates?

**Logical999** · 01-17-2018, 11:47 AM

Thank you for taking the time to reply.
I am sorry - I do not understand how to do that? Can you point me at a guide or explain a bit further. Sorry my level of competence is low.

The cells aren't complete duplicates, I want to remove the cells that have duplicate text within "".

**queuesef** · 01-17-2018, 12:23 PM

another way is to copy your data to a blank column. Then use Text to columns (Data, text to columns) where you can SPLIT the data (either by fixed length OR Delimited). You can then use Conditional formatting, to Highlight duplicate values, then remove the duplicates.

**Logical999** · 01-21-2018, 08:41 PM

Ok, So text to columns works in excel 2003.
But (1) I don't know how to remove duplicates - i tried data filter advanced filter, but doesn't seem to work properly.
(2) even if I could remove duplicates, I don't then know how to get the columns back to text again.
I'm happy to buy a later version of excel if this sorts the issue out.

**JeteMc** · 01-23-2018, 11:28 AM

Hello Logical999 and Welcome to Excel Forum.
Here is a low tech solution, however I believe that all of the functions involved are available to the 2003 version.
The proposed solution employs three helper columns which may be moved and/or hidden for aesthetic purposes.
The first helper column is populated using: =MID(A2,SEARCH("=",A2)+1,SEARCH("label",A2)-SEARCH("=",A2)-1)
The second helper column is populated using: =IF(COUNTIF(D$2:D2,D2)>1,"",D2)
The third helper column is populated using: =IF(E2<>"",A2,"")
The column that contains the result ('After') is populated using*:

Formula:

Please Login or Register  to view this content.

*Denotes an array entered formula which needs to be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER before copying to other cells. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
If this solution isn't applicable to your data please upload the spreadsheet from which the screen shot in post #1 was made. To upload a spreadsheet click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window.
Let us know if you have any questions.

**Logical999** · 01-23-2018, 02:00 PM

That works great. Thank you so much.

**JeteMc** · 01-23-2018, 04:32 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.