Note: The other languages of the website are Google-translated. Back to English
Σύνδεση  \/ 
x
or
x
Εγγραφή  \/ 
x

or

Πώς να αφαιρέσετε διπλούς χαρακτήρες ή λέξεις στη συμβολοσειρά ενός κελιού;

Ας υποθέσουμε ότι υπάρχουν διπλοί χαρακτήρες ή λέξεις σε κάθε κελί και θέλετε να καταργήσετε τα διπλότυπα και να αφήσετε μόνο μοναδικούς χαρακτήρες ή λέξεις. Πώς θα μπορούσατε να λύσετε αυτό το πρόβλημα στο Excel;

Καταργήστε διπλούς χαρακτήρες συμβολοσειράς κειμένου με τη λειτουργία που καθορίζεται από το χρήστη

Καταργήστε τις διπλές λέξεις που διαχωρίζονται με σημεία στίξης με τη λειτουργία καθορισμένη από το χρήστη


βέλος μπλε δεξιά φούσκα Καταργήστε διπλούς χαρακτήρες συμβολοσειράς κειμένου με τη λειτουργία που καθορίζεται από το χρήστη

Εάν έχετε μια λίστα συμβολοσειρών κειμένου, τώρα, πρέπει να καταργήσετε τους διπλούς χαρακτήρες όπως φαίνεται στο παρακάτω στιγμιότυπο οθόνης. Εδώ, μπορώ να μιλήσω για έναν κωδικό VBA για να το αντιμετωπίσω.

doc-remove-duplicate-χαρακτήρες-1

1. Κρατήστε πατημένο το ALT + F11 για να ανοίξετε το Παράθυρο Microsoft Visual Basic for Applications.

2. Κλίκ Κύριο θέμα > Μονάδα μέτρησηςκαι επικολλήστε τον ακόλουθο κώδικα στο Παράθυρο ενότητας.

Κωδικός VBA: Κατάργηση διπλών χαρακτήρων συμβολοσειράς κειμένου σε ένα κελί

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. Στη συνέχεια, αποθηκεύστε και κλείστε αυτόν τον κωδικό, επιστρέψτε στο φύλλο εργασίας και εισαγάγετε αυτόν τον τύπο = removeupes1 (A2) (A2 είναι το κελί δεδομένων από το οποίο θέλετε να καταργήσετε διπλότυπα) σε ένα κενό κελί εκτός από τα δεδομένα σας, δείτε το στιγμιότυπο οθόνης:

doc-remove-duplicate-χαρακτήρες-1

4. Και, στη συνέχεια, σύρετε τη λαβή πλήρωσης στα κελιά εύρους που θέλετε να εφαρμόσετε αυτόν τον τύπο, όλοι οι διπλότυποι χαρακτήρες έχουν αφαιρεθεί αμέσως από κάθε κελί.

doc-remove-duplicate-χαρακτήρες-1


βέλος μπλε δεξιά φούσκα Καταργήστε τις διπλές λέξεις που διαχωρίζονται με σημεία στίξης με τη λειτουργία καθορισμένη από το χρήστη

Εάν υπάρχουν κάποιες λέξεις που χωρίζονται από ορισμένα σημεία στίξης σε ένα κελί, μπορείτε επίσης να καταργήσετε τις διπλές λέξεις όπως θέλετε. Κάντε τα παρακάτω βήματα:

1. Κρατήστε πατημένο το ALT + F11 για να ανοίξετε το Παράθυρο Microsoft Visual Basic for Applications.

2. Κλίκ Κύριο θέμα > Μονάδα μέτρησηςκαι επικολλήστε τον ακόλουθο κώδικα στο Παράθυρο ενότητας.

Κωδικός VBA: Κατάργηση διπλών λέξεων που διαχωρίζονται με σημεία στίξης ενός κελιού

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. Στη συνέχεια, αποθηκεύστε και κλείστε αυτόν τον κωδικό, επιστρέψτε στο φύλλο εργασίας και εισαγάγετε αυτόν τον τύπο = RemoveDupes2 (A2, ",") σε ένα κενό κελί δίπλα στα δεδομένα σας, δείτε το στιγμιότυπο οθόνης:

doc-remove-duplicate-χαρακτήρες-1

4. Στη συνέχεια, αντιγράψτε τον τύπο στα κελιά που χρειάζεστε και όλες οι διπλότυπες λέξεις έχουν αφαιρεθεί από κάθε κελί. Δείτε το στιγμιότυπο οθόνης:

doc-remove-duplicate-χαρακτήρες-1

Note: Στον παραπάνω τύπο, A2 υποδεικνύει το κελί που θέλετε να χρησιμοποιήσετε και το κόμμα (,) σημαίνει το σημείο στίξης που διαχωρίζει τις λέξεις σε ένα κελί, μπορείτε να το αλλάξετε σε οποιαδήποτε άλλα σημεία στίξης ανάλογα με τις ανάγκες σας.


Τα καλύτερα εργαλεία παραγωγικότητας του Office

Το Kutools για Excel λύνει τα περισσότερα από τα προβλήματά σας και αυξάνει την παραγωγικότητά σας κατά 80%

  • Επαναχρησιμοποίηση: Εισαγάγετε γρήγορα σύνθετοι τύποι, γραφήματα και οτιδήποτε έχετε χρησιμοποιήσει στο παρελθόν. Κρυπτογράφηση κυττάρων με κωδικό πρόσβασης Δημιουργία λίστας αλληλογραφίας και στείλτε email ...
  • Super Formula Bar (επεξεργαστείτε εύκολα πολλές γραμμές κειμένου και τύπου). Διάταξη ανάγνωσης (εύκολη ανάγνωση και επεξεργασία μεγάλου αριθμού κελιών). Επικόλληση σε φιλτραρισμένο εύρος...
  • Συγχώνευση κελιών / σειρών / στηλών χωρίς απώλεια δεδομένων · Περιεχόμενο διαχωρισμού κελιών Συνδυάστε διπλές σειρές / στήλες... Αποτροπή διπλών κυττάρων; Συγκρίνετε τα εύρη...
  • Επιλέξτε Διπλότυπο ή Μοναδικό Σειρές; Επιλέξτε Κενές σειρές (όλα τα κελιά είναι κενά). Σούπερ εύρεση και ασαφής εύρεση σε πολλά βιβλία εργασίας. Τυχαία επιλογή ...
  • Ακριβές αντίγραφο Πολλαπλά κελιά χωρίς αλλαγή της αναφοράς τύπου. Αυτόματη δημιουργία αναφορών σε πολλαπλά φύλλα? Εισαγωγή κουκκίδων, Πλαίσια ελέγχου και άλλα ...
  • Εξαγωγή κειμένου, Προσθήκη κειμένου, Κατάργηση κατά θέση, Αφαιρέστε το διάστημα; Δημιουργία και εκτύπωση υποσύνολων σελιδοποίησης. Μετατροπή περιεχομένου και σχολίων μεταξύ κελιών...
  • Σούπερ φίλτρο (αποθηκεύστε και εφαρμόστε σχήματα φίλτρων σε άλλα φύλλα). Προηγμένη ταξινόμηση ανά μήνα / εβδομάδα / ημέρα, συχνότητα και άλλα. Ειδικό φίλτρο με έντονη, πλάγια ...
  • Συνδυάστε βιβλία εργασίας και φύλλα εργασίας; Συγχώνευση πινάκων βάσει βασικών στηλών. Διαχωρίστε τα δεδομένα σε πολλά φύλλα; Μαζική μετατροπή xls, xlsx και PDF...
  • Περισσότερα από 300 ισχυρά χαρακτηριστικά. Υποστηρίζει Office / Excel 2007-2019 και 365. Υποστηρίζει όλες τις γλώσσες. Εύκολη ανάπτυξη στην επιχείρηση ή τον οργανισμό σας. Πλήρεις δυνατότητες δωρεάν δοκιμής 30 ημερών. Εγγύηση επιστροφής χρημάτων 60 ημερών.
kte καρτέλα 201905

Το Office Tab φέρνει τη διεπαφή με καρτέλες στο Office και κάνει την εργασία σας πολύ πιο εύκολη

  • Ενεργοποίηση επεξεργασίας και ανάγνωσης καρτελών σε Word, Excel, PowerPoint, Publisher, Access, Visio και Project.
  • Ανοίξτε και δημιουργήστε πολλά έγγραφα σε νέες καρτέλες του ίδιου παραθύρου και όχι σε νέα παράθυρα.
  • Αυξάνει την παραγωγικότητά σας κατά 50% και μειώνει εκατοντάδες κλικ ποντικιού για εσάς κάθε μέρα!
κάτω μέρος γραφείου
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    El Conquistador · 2 months ago
    Not sure if this thread is still alive. I am trying to use this for something as simple as what is shown in the chart above and I keep on receiving #NAME? error. Cell A2 has XXX, YYY, XXX, AAA I can't quite understand what I am doing wrong. I have the code in my personal workbook. Would that be the issue?

  • To post as a guest, your comment is unpublished.
    Tapomoy Chakraborty · 9 months ago
    Thank you for the beautiful and useful coding. Can we do the same using Power Query?
  • To post as a guest, your comment is unpublished.
    Stefan · 1 years ago
    Hi, is there a way to get the result of "RemoveDupes2" alphabetically sorted?
  • To post as a guest, your comment is unpublished.
    baana2005 · 1 years ago
    Hey! can anyone help me,
    I have a data in excel example:



    CARITAS, CSCED, FREE, NRC
    BOSADP, DHCBI
    BOSADP, Mercy Corps, NRC
    ADRA, NRC
    BOSADP, CAID
    AAH, FAO (NEYIF), CRS (NEYIF)
    SWNI, Mercy Corps,
    CARE, PLAN, NRC
    Mercy Corps, NRC
    BOSADP, DHCBI, GREENCODE, NRC, AAH BOSADP


    What I really want

    CARITAS
    BOSADP
    DHCBI
    NRC
    ADRA
    CAID
    AAH
    FAO
    NEYIF
    CRS
    SWNI
    CARE
    PLAN
    Mercy Corps
    GREENCODE
    FREE


    the repeated words separated by comma deleted and and those that not repeated but separated by comma move to the next columns
  • To post as a guest, your comment is unpublished.
    Ali Maimadu Barma · 1 years ago
    Hey! can anyone help me,
    I have a data in excel example:



    CARITAS, CSCED, FREE, NRC
    BOSADP, DHCBI
    BOSADP, Mercy Corps, NRC
    ADRA, NRC
    BOSADP, CAID
    AAH, FAO (NEYIF), CRS (NEYIF)
    SWNI, Mercy Corps,
    CARE, PLAN, NRC
    Mercy Corps, NRC
    BOSADP, DHCBI, GREENCODE, NRC, AAH BOSADP


    What I really want

    CARITAS
    BOSADP
    DHCBI
    NRC
    ADRA
    CAID
    AAH
    FAO
    NEYIF
    CRS
    SWNI
    CARE
    PLAN
    Mercy Corps
    GREENCODE
    FREE


    the repeated words separated by comma deleted and and those that not repeated but separated by comma move to the next columns
  • To post as a guest, your comment is unpublished.
    deezy · 1 years ago
    i tried applying method2 to numbers but it does not work. eg. 1104, 1104, 203, 203, 409, 409 >>>1104, 203, 409 How do you get method 2 to work with numbers?

    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, deezy,
      The second method works well for numbers, please see the below screenshot.
      Please try it again, thank you!
  • To post as a guest, your comment is unpublished.
    Yael · 1 years ago
    Thanks so much for sharing knowledge, I used the "Remove Duplicate Words Separated By Punctuation With User Defined Function" and it worked great. May you knowledge grow and grow.
  • To post as a guest, your comment is unpublished.
    orine89@gmail.com · 1 years ago
    how about to remove duplicate character with an exception

    for example from MMMPXLL, i want to remove M but keep the L

    so the result will be MPXLL
    • To post as a guest, your comment is unpublished.
      orine89@gmail.com · 1 years ago
      Edit :

      some problem solved with removedupes2, but i have to do it one by one

      for example i cant remove duplicate letter y,a,l at once. I have to do it one by one.

      also, for case there is data like this yyypl, it become pl, the "y" is totally removed
  • To post as a guest, your comment is unpublished.
    s.woods@carsparesltd.com · 1 years ago
    i have a string of text like so

    1 Ser 1 Ser 1 Ser 1 Ser 1 Ser 1 Ser 1 Ser 1 Ser 1 Ser 2 Ser 2 Ser 2 Ser 2 Ser 2 Ser 2 Ser 2 Ser 2 Ser 3 Ser 3 Ser 3 Ser 3 Ser 3 Ser 3 Ser 3 Ser 3 Ser X3 X5

    when applying remove dupes 2 i get

    1 Ser 2 3 X3 X5


    by the looks of it, all duplicates except the first instance are kept, is there any way to tweek this so all duplicate instances except the last occurance are kept ?

    so my desired outcome would be

    1 2 3 Ser X3 X5

    thank you in advanced!
  • To post as a guest, your comment is unpublished.
    Ujjwal · 1 years ago
    thanks for the info.

    Can we also remove both duplicate values?
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Ujjwal,
      To remove all duplicate values and only keep the uniques, please use the below code:

      Function RemoveDuplicateValue(xDStr As String, xDelim As String) As String
      Dim xValue
      Dim xB As Boolean
      Dim xArr As Variant
      Dim xF, xFF As Integer
      Dim xUB As Integer
      Dim xStr, xRStr As String
      If (Len(xDelim) > 0) And (Len(Trim(xDStr)) > 0) Then
      xArr = Split(xDStr, xDelim)
      xUB = UBound(xArr)
      For xF = 0 To xUB - 1
      xStr = xArr(xF)
      If xStr <> "" Then
      xB = False
      For xFF = xF + 1 To xUB
      If UCase(xStr) = UCase(xArr(xFF)) Then
      xB = True
      xArr(xFF) = ""
      End If
      Next
      If xB Then xArr(xF) = ""
      End If
      Next
      xRStr = ""
      For Each xStr In xArr
      If xStr <> "" Then xRStr = xRStr & xStr & xDelim
      Next
      xRStr = Mid(xRStr, 1, Len(xRStr) - 1)
      Else
      xRStr = ""
      End If
      RemoveDuplicateValue = xRStr
      End Function

      And then use this formula: =RemoveDuplicateValue(A1,",").
  • To post as a guest, your comment is unpublished.
    Ivan · 1 years ago
    Thank you for the information! Very useful!
    I need help for the output as below:
    CODE, POST, GRADE, POST, GRADE, GRADE ------> CODE, POST, GRADE
    I need to remain with only one of each presented. Please assist. Thanks in advance!
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Ivan,
      To solve your problem, method 2 in this article can help you! Please try, thank you!
      • To post as a guest, your comment is unpublished.
        Ivan · 1 years ago
        Hi Skyyang,
        It worked indeed, many thanks!!! Initially It didnt work cuz i had "space" after the delimiters and that "space" should be put when typing the formula too. However, many thanks!!!
  • To post as a guest, your comment is unpublished.
    vinay · 1 years ago
    thank you so much for information
    the VBA code RemoveDupes2
    i need the output as below
    Code,post,code,guest,code,location ----> post,guest,location


    could you please suggest. I really appreciate your help
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, vinay,
      To remove all duplicate values and only keep the uniques, please use the below code:

      Function RemoveDuplicateValue(xDStr As String, xDelim As String) As String
      Dim xValue
      Dim xB As Boolean
      Dim xArr As Variant
      Dim xF, xFF As Integer
      Dim xUB As Integer
      Dim xStr, xRStr As String
      If (Len(xDelim) > 0) And (Len(Trim(xDStr)) > 0) Then
      xArr = Split(xDStr, xDelim)
      xUB = UBound(xArr)
      For xF = 0 To xUB - 1
      xStr = xArr(xF)
      If xStr <> "" Then
      xB = False
      For xFF = xF + 1 To xUB
      If UCase(xStr) = UCase(xArr(xFF)) Then
      xB = True
      xArr(xFF) = ""
      End If
      Next
      If xB Then xArr(xF) = ""
      End If
      Next
      xRStr = ""
      For Each xStr In xArr
      If xStr <> "" Then xRStr = xRStr & xStr & xDelim
      Next
      xRStr = Mid(xRStr, 1, Len(xRStr) - 1)
      Else
      xRStr = ""
      End If
      RemoveDuplicateValue = xRStr
      End Function

      And then use this formula: =RemoveDuplicateValue(A1,",").
      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        vinay · 1 years ago
        Thanks

        I really appreciate your help
  • To post as a guest, your comment is unpublished.
    jenny · 1 years ago
    How can I remove the word dealer code in every word in each cell and just leave the number.
    Like the following:
    DEALER CODE A123
    DEALER CODE B456
    DEALER CODE C789
    DEALER CODE D012

    Result needed is:
    A123
    B456
    C789
    D012
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, jenny,
      To remove the first x characters from a cell, please apply the below formula:
      =RIGHT(A1, LEN(A1)-12)

      Please try, hope it can help you!
  • To post as a guest, your comment is unpublished.
    · 1 years ago
    Disturbing Excel formula


    If column A1 = R10, R11, R12, R13, R14, R15, R16, R17


    And column B1 = R10, R11, R19, R13, R14, R18, R20, R17


    Want the column C1 to find the difference


    Will get column C1 = R12, R19, R15, R16, R18, R20


    How to use the formula?
  • To post as a guest, your comment is unpublished.
    Rocio · 2 years ago
    Hi, what should I do if I want to delete duplicated words within on cell? For example, Ana Garcia Lopes Garcia -> Ana Garcia Lopes
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Rocio,
      To remove the duplicate words within a cell, you should apply the User Defined Function of the second method in this article,
      Remove duplicate words separated by punctuation with User Defined Function

      and then apply this formula:=RemoveDupes2(A2," ").
      Please try it, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Rocio · 2 years ago
        Thanks! but what will happen if i do not have punctuation within the cell? for example: Apple Book Pencil Book and I want to remove the duplicated word in this case Book and just obtain Apple Book Pencil
        • To post as a guest, your comment is unpublished.
          skyyang · 2 years ago
          Hi,
          If your cell value is separated by space, after pasting the above code, you just need to replace the comma with space as follows:
          Replace this formula:=RemoveDupes2(A2,",") with =RemoveDupes2(A2," ")
  • To post as a guest, your comment is unpublished.
    Bill Davis · 2 years ago
    I have been using the VBA code: Remove duplicate words separated by punctuation of a cell RemoveDupes2 logic for some time now and I recently had my machine upgraded to O365 and when running this same process, I get an error on my cell. Instead of returning the values I expect, I am getting the #Value! error in the cell.

    I'm not too savy on writing formulas or using the VB logic, so not sure where to start, any guidance would be helpful.
  • To post as a guest, your comment is unpublished.
    NeedExcelHelp · 2 years ago
    What about removing duplicate addresses. If I have a cell that says "123 Address St. 123 Address St." How can I select only the first address when I have many cells with different addresses in each of them?
  • To post as a guest, your comment is unpublished.
    Suraj Singh · 2 years ago
    Can we removed the duplicate from in single cell tax . like as per below tax the highlighted tax is duplicate.

    C2/1104, SUMMER PALM SECTOR 86 C2/1104, SUMMER PALM FARIDABAD HARYANA India
  • To post as a guest, your comment is unpublished.
    angshumanp02@gmail.com · 3 years ago
    how to save =RemoveDupes2 this formula in excel 2007
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Pradhan,
      To save this formula for using next time, you should save the workbook as Excel Macro-Enabled Workbook file format.
      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Alonso · 3 years ago
    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.
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Mike W · 2 years ago
        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.
        • To post as a guest, your comment is unpublished.
          skyyang · 2 years ago
          Hi, Michael,
          Do you mean to remove all duplicate phrases from a cell, and only leave the unique ones?
          Look forward to your reply!
          • To post as a guest, your comment is unpublished.
            Mike W · 2 years ago
            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.
            • To post as a guest, your comment is unpublished.
              skyyang · 2 years ago
              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
              • To post as a guest, your comment is unpublished.
                Mike W · 2 years ago
                Thank you so very much. Your response was more welcome than my cup of coffee!
                • To post as a guest, your comment is unpublished.
                  mahesh · 11 months ago
                  Hi this is real helpful
                  But i want to remove entire words after "NO TO:"

                  eg: NO TO: [C11bx1]

                  NO TO: [C11bx3] like this

  • To post as a guest, your comment is unpublished.
    kmritul@gmail.com · 3 years ago
    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.
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Imran,
      Sorry for not solving your problem, if anyone has the good idea, please comment here.
      Thank you!
      • To post as a guest, your comment is unpublished.
        kmritul@gmail.com · 3 years ago
        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.
  • To post as a guest, your comment is unpublished.
    Kiran · 3 years ago
    How to remove matched string duplicates...can u pls tell me


    Regards,
    Kiran
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello, Kiran,
      Can you give an example of your problem? Or you can insert an attachment image.
      Looking forward to your reply!
  • To post as a guest, your comment is unpublished.
    Meharban singh pal · 3 years ago
    can we find out position of 1st Lower case letter


    Exam - RAMSHsJHSAhsjDDD ------I want to know position of 1st small letter by formula = s
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      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!
      • To post as a guest, your comment is unpublished.
        Meharban singh pal · 3 years ago
        Hi skyyang

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

        many thanks !!


        Meharban singh pal
  • To post as a guest, your comment is unpublished.
    Hanuman Singh · 4 years ago
    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
    • To post as a guest, your comment is unpublished.
      Kring · 3 years ago
      teach me how? need to know it too..
  • To post as a guest, your comment is unpublished.
    Mitch L · 4 years ago
    Thanks for this code - it saved me a ton of time.
  • To post as a guest, your comment is unpublished.
    Simon Alexander · 4 years ago
    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!
  • To post as a guest, your comment is unpublished.
    Archana Nahak · 4 years ago
    This is very helpful . I have tried it and it works for me as excepted.
    Excellent !!!!
  • To post as a guest, your comment is unpublished.
    Alastair · 4 years ago
    Just like to save thank you so much for this guide, saved me a lot of work! :)
  • To post as a guest, your comment is unpublished.
    Eka Sari · 4 years ago
    Is it possible to adjust the function if there is a certain word I want to keep duplicated while the rest are not duplicated?
  • To post as a guest, your comment is unpublished.
    Joni · 4 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Suresh · 4 years ago
    Awesome, Thank you!.....
  • To post as a guest, your comment is unpublished.
    Leila · 5 years ago
    BRILLIANT
    thank you! thank you! thank you!
  • To post as a guest, your comment is unpublished.
    Rana · 5 years ago
    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
    • To post as a guest, your comment is unpublished.
      deezy · 1 years ago
      same here pls help
  • To post as a guest, your comment is unpublished.
    Charlie Keyloe · 5 years ago
    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.
  • To post as a guest, your comment is unpublished.
    john galt · 6 years ago
    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?
  • To post as a guest, your comment is unpublished.
    Lisa Robinson · 6 years ago
    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?
    • To post as a guest, your comment is unpublished.
      deezy · 1 years ago
      same here pls help
  • To post as a guest, your comment is unpublished.
    milworker · 6 years ago
    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
  • To post as a guest, your comment is unpublished.
    milworker · 6 years ago
    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....
  • To post as a guest, your comment is unpublished.
    milworker · 6 years ago
    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?