Note: The other languages of the website are Google-translated. Back to English
English English

How to remove duplicate characters or words in string of a cell?

Supposing there are duplicate characters or words in each cell, and you want to remove the duplicates and leave only unique characters or words. How could you solve this problem in Excel?

Remove duplicate characters of text string with User Defined Function

Remove duplicate words separated by punctuation with User Defined Function


arrow blue right bubble Remove duplicate characters of text string with User Defined Function

If you have a list of text string, now, you need to remove the duplicate characters as following screenshot shown. Here, I can talk about a VBA code to deal with it.

doc-remove-duplicate-characters-1

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Remove duplicate characters of text string in a cell

Function RemoveDupes1(pWorkRng As Range) As String
'Updateby Extendoffice
Dim xValue As String
Dim xChar As String
Dim xOutValue As String
Set xDic = CreateObject("Scripting.Dictionary")
xValue = pWorkRng.Value
For i = 1 To VBA.Len(xValue)
    xChar = VBA.Mid(xValue, i, 1)
    If xDic.Exists(xChar) Then
    Else
        xDic(xChar) = ""
        xOutValue = xOutValue & xChar
    End If
Next
RemoveDupes1 = xOutValue
End Function

3. Then save and close this code, go back to the worksheet, and enter this formula =removedupes1(A2) (A2 is the data cell you want to remove duplicates from) into a blank cell besides your data, see screenshot:

doc-remove-duplicate-characters-1

4. And then drag the fill handle over to the range cells that you want to apply this formula, all the duplicate characters have been removed from each cell immediately.

doc-remove-duplicate-characters-1


arrow blue right bubble Remove duplicate words separated by punctuation with User Defined Function

If there are some words which are separated by certain punctuation marks in a cell, you can also remove the duplicate words as you like. Please do with the following steps:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Remove duplicate words separated by punctuation of a cell

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
    Dim x
    'Updateby Extendoffice
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
    End With
End Function

3. Then save and close this code, return to the worksheet, and enter this formula =RemoveDupes2(A2,",") into a blank cell next to your data, see screenshot:

doc-remove-duplicate-characters-1

4. Then copy down the formula to your needed cells, and all the duplicate words have been removed from every cells. See screenshot:

doc-remove-duplicate-characters-1

Note: In the above formula, A2 indicates the cell that you want to use, and the comma (,) stands for the punctuation mark which separates the words in a cell, you can change it to any other punctuations to your need.


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (66)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Still broke... doesn't actually show anything. Here's my cell data: 27000,Basic,Products,Materials,From,Wood,Stone,Earthen,Raw,Materials,27100,Basic,Forest,Products,General,27110,Basic,Forest,Products,Sawmill,Products,27120,Basic,Forest,Products,Wood,Pulp,27200,Glass Clearly have some dupes there. Leaves the cell blank. Thoughts?
This comment was minimized by the moderator on the site
Nevermind, I'm dumb. Because I only wanted words pulled I renamed the module RemoveDupes, but didn't change the RemoveDupes later in the code. Fixed, smacking self with hammer....
This comment was minimized by the moderator on the site
And I combined RemoveDupes with ConCat (the VBA goodness, not CON(I-SUCK-BECAUSE-I'M-MICROSOFT)CATENATE) into this: =RemoveDupes(ConCat(",",G495:G502),",") so it pulled my cells in and then wiped all of the dupes out. Thanks for the RemoveDupes, here's ConCat: Function ConCat(Delimiter As Variant, ParamArray CellRanges() As Variant) As String Dim Cell As Range, Area As Variant If IsMissing(Delimiter) Then Delimiter = "" For Each Area In CellRanges If TypeName(Area) = "Range" Then For Each Cell In Area If Len(Cell.Value) Then ConCat = ConCat & Delimiter & Cell.Value Next Else ConCat = ConCat & Delimiter & Area End If Next ConCat = Mid(ConCat, Len(Delimiter) + 1) End Function
This comment was minimized by the moderator on the site
I tried your solution "Remove duplicate words separated by punctuation with User Defined Function" and it works great for multiple words in a cell BUT doesn't completely solve my problem. I have one cell that has multiple values and want to compare against another cell with multiple values and then have it remove the duplicates that exist between the two cells. Is there a way to do that?
This comment was minimized by the moderator on the site
same here pls help
This comment was minimized by the moderator on the site
is this thread still alive? I would greatly appreciate some insight with this VBA function.. my questions has to do with the code, where if at all do i need to instruct it to find the specific punctuation as the delimiter.. if I were to use "@" as the punctuation?
This comment was minimized by the moderator on the site
Your advice is amazing! I sorted 58,400 records in about 2 minutes and saved one week (or more) of work. My duplicates were a bit harder turning this example " V&O Liquid Fuel Lantern 13-1/2in blk fuel lantern " into this " V&O Liquid Fuel Lantern 13-1/2in blk " thank you.
This comment was minimized by the moderator on the site
Hello, please I need your help, i tried your code but it didnt work, maybe because I have numbers I have duplicate telephone numbers separated by commas Please tell me what to do Thanks
This comment was minimized by the moderator on the site
same here pls help
This comment was minimized by the moderator on the site
BRILLIANT thank you! thank you! thank you!
This comment was minimized by the moderator on the site
Awesome, Thank you!.....
This comment was minimized by the moderator on the site
Hy dude i need your help. What should i do if iwant extract the duplicate? In A1 (asdfghjiklkk) So k is duplicated character I want A2 (k) what should i do?
This comment was minimized by the moderator on the site
Is it possible to adjust the function if there is a certain word I want to keep duplicated while the rest are not duplicated?
This comment was minimized by the moderator on the site
Just like to save thank you so much for this guide, saved me a lot of work! :)
This comment was minimized by the moderator on the site
This is very helpful . I have tried it and it works for me as excepted. Excellent !!!!
This comment was minimized by the moderator on the site
This is an awesome and simple solution to finding duplicate phrases within a single cell, much easier than some of the long-winded macro solutions I found while looking for an answer to my problem, thank you!
This comment was minimized by the moderator on the site
Thanks for this code - it saved me a ton of time.
This comment was minimized by the moderator on the site
I have data like 1. show below in a single cell and I want to like 2. 1. ZERPUR,MAHENDRAGARH, HARYANA-123029, 30771237, 8813073653, ZERPUR(16),MAHENDRAGARH, HARYANA-123029, 30771237, 8813073653 I need this like :- ZERPUR,MAHENDRAGARH, HARYANA-123029, 30771237, 8813073653 Thanks & Regards Hanuman Singh 9034509168
This comment was minimized by the moderator on the site
teach me how? need to know it too..
This comment was minimized by the moderator on the site
can we find out position of 1st Lower case letter


Exam - RAMSHsJHSAhsjDDD ------I want to know position of 1st small letter by formula = s
This comment was minimized by the moderator on the site
Hello, Meharban,
You can apply the following array formula to get the position of the first lower case letter:
=MATCH(1,IF(ABS(CODE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))-109.5)<=12.5,1),0)
Please remember to press Ctrl + Shift + Enter keys together.


Hope it can help you ,thank you!
This comment was minimized by the moderator on the site
Hi skyyang

it's working properly .....
actually i wanted same thing..

many thanks !!


Meharban singh pal
This comment was minimized by the moderator on the site
How to remove matched string duplicates...can u pls tell me


Regards,
Kiran
This comment was minimized by the moderator on the site
Hello, Kiran,
Can you give an example of your problem? Or you can insert an attachment image.
Looking forward to your reply!
This comment was minimized by the moderator on the site
thanks a lot, it helps me more. But I want some specific more for my dataset.
I have two Columns data. ID and Info. In Info Column, per cell project-id like "Kant-, Udp-, Akr- etc" are repeated. For my dataset the project_id is more than 15 and It fixed. So I want to delete the repeated_id from cell but keep the first one.
Examle: Kant-526 (0.0287),Kant-527 (0.0113),Kant-528 (0.0262) /// Kant-526 (0.0287),527 (0.0113),528 (0.0262),
Kant-543 (0.0685),Kant-544 (0.0685),Udp-097 (0.0141) /// Kant-543 (0.0685),544 (0.0685),Udp-097 (0.0141)

Sample image is attached here. Is there any solution for this.
This comment was minimized by the moderator on the site
Hello, Imran,
Sorry for not solving your problem, if anyone has the good idea, please comment here.
Thank you!
This comment was minimized by the moderator on the site
I have got a solution by my own. I have used SUBSTITUTE formula here, but it is too large for my data. so repeat it two or three times for getting the proper result.

=SUBSTITUTE(SUBSTITUTE("Kant-","",2), ("Kant-","",2)
Here, 1st 2 for second Kant- replacement and for next every Kant- removing just add ("Kant-","",2) extra. Its working for me. But I prefer a good VBA.
This comment was minimized by the moderator on the site
Thanks for the code.

Can you please help me? I am using the macro function to remove words separated by punctuation of a cell, however my data is not words is actually phrases. Like instead of "KTE, KTO, KTW, KTO" is actually short descriptions like "prepare a bill, review my emails, update the contracts, review my emails".

Would it be possible to update the VBA to remove duplicated phrases instead of words?

Many thanks, this will be actually very helpful.
This comment was minimized by the moderator on the site
Hi, Alonso,
To remove duplicate phrase, the following User Defined Function may help you, please try it.

Function RemoveDuplicateValue(xStr As String, xDelim As String) As String
Dim xValue
If (Len(xDelim) > 0) And (Len(Trim(xStr)) > 0) Then
With CreateObject("Scripting.Dictionary")
For Each xValue In Split(xStr, xDelim)
If Trim(xValue) <> "" And Not .exists(Trim(xValue)) Then .Add Trim(xValue), Nothing
Next
If .Count > 0 Then RemoveDuplicateValue = Join(.keys, xDelim)
End With
Else
RemoveDuplicateValue = xStr
End If
End Function

Then apply this formula: =RemoveDuplicateValue(A2,","), change the separator , to your own.
This comment was minimized by the moderator on the site
Hello skyyang,



I am hoping to get some help with the following cell:

PR-PUERTO RICO; US-UNITED STATES; US-UNITED STATES

I need the duplicate US-UNITED STATES removed as my upload tool will not allow for duplicates.



I used the above VBA and replaced my formula deliminator with ";". Is the hyphen causing this to have an issue? It returns #NAME? whenever I run the formula. I am new to macros/VBA but I made sure the file was saved as a Macro Enabled file. I really tried to follow these to the T.


Thank you for all of this already great information as it has helped with another project I had to complete as well and anything additional is greatly appreciated.
This comment was minimized by the moderator on the site
Hi, Michael,
Do you mean to remove all duplicate phrases from a cell, and only leave the unique ones?
Look forward to your reply!
This comment was minimized by the moderator on the site
This is the output I am looking for:

Current: PR-PUERTO RICO; US-UNITED STATES; US-UNITED STATES
Desired: PR-PUERTO RICO; US-UNITED STATES

Hope this makes sense and thank you very much for the quick reply.
This comment was minimized by the moderator on the site
Hi, Michael,
You just need to apply the below code, and then use this formula: =RemoveDuplicateValue(A2,";"). Please try, hope it can help you!

Function RemoveDuplicateValue(xStr As String, xDelim As String) As String
Dim xValue
If (Len(xDelim) > 0) And (Len(Trim(xStr)) > 0) Then
With CreateObject("Scripting.Dictionary")
For Each xValue In Split(xStr, xDelim)
If Trim(xValue) <> "" And Not .exists(Trim(xValue)) Then .Add Trim(xValue), Nothing
Next
If .Count > 0 Then RemoveDuplicateValue = Join(.keys, xDelim)
End With
Else
RemoveDuplicateValue = xStr
End If
End Function
This comment was minimized by the moderator on the site
Thank you so very much. Your response was more welcome than my cup of coffee!
This comment was minimized by the moderator on the site
Hi this is real helpful
But i want to remove entire words after "NO TO:"

eg: NO TO: [C11bx1]

NO TO: [C11bx3] like this
There are no comments posted here yet
Load More
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations