Skip i'r prif gynnwys

Sut i gael gwared ar nodau neu eiriau dyblyg mewn llinyn o gell?

Gan dybio bod nodau neu eiriau dyblyg ym mhob cell, a'ch bod am gael gwared ar y dyblygu a gadael cymeriadau neu eiriau unigryw yn unig. Sut allech chi ddatrys y broblem hon yn Excel?

Tynnwch nodau dyblyg llinyn testun gyda Swyddogaeth Diffiniedig Defnyddiwr

Tynnwch eiriau dyblyg wedi'u gwahanu gan atalnodi â Swyddogaeth Diffiniedig Defnyddiwr


swigen dde glas saeth Tynnwch nodau dyblyg llinyn testun gyda Swyddogaeth Diffiniedig Defnyddiwr

Os oes gennych chi restr o linyn testun, nawr, mae angen i chi gael gwared ar y nodau dyblyg fel y llun a ganlyn a ddangosir. Yma, gallaf siarad am god VBA i ddelio ag ef.

doc-remove-duplicate-character-1

1. Daliwch i lawr y ALT + F11 allweddi i agor y Ffenestr Microsoft Visual Basic for Applications.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.

Cod VBA: Tynnwch nodau dyblyg llinyn testun mewn 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. Yna arbed a chau'r cod hwn, ewch yn ôl i'r daflen waith, a nodi'r fformiwla hon = removedupes1 (A2) (A2 yw'r gell ddata rydych chi am dynnu dyblygu ohoni) i mewn i gell wag ar wahân i'ch data, gweler y screenshot:

doc-remove-duplicate-character-1

4. Ac yna llusgwch y handlen llenwi drosodd i'r celloedd amrediad rydych chi am gymhwyso'r fformiwla hon, mae'r holl nodau dyblyg wedi'u tynnu o bob cell ar unwaith.

doc-remove-duplicate-character-1


swigen dde glas saeth Tynnwch eiriau dyblyg wedi'u gwahanu gan atalnodi â Swyddogaeth Diffiniedig Defnyddiwr

Os oes rhai geiriau sydd wedi'u gwahanu gan farciau atalnodi penodol mewn cell, gallwch hefyd gael gwared ar y geiriau dyblyg fel y dymunwch. Gwnewch y camau canlynol:

1. Dal i lawr y ALT + F11 allweddi i agor y Ffenestr Microsoft Visual Basic for Applications.

2. Cliciwch Mewnosod > Modiwlau, a gludwch y cod canlynol yn y Ffenestr Modiwl.

Cod VBA: Tynnwch eiriau dyblyg wedi'u gwahanu gan atalnodi 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. Yna arbed a chau'r cod hwn, dychwelyd i'r daflen waith, a nodi'r fformiwla hon = RemoveDupes2 (A2, ",") i mewn i gell wag wrth ymyl eich data, gweler y screenshot:

doc-remove-duplicate-character-1

4. Yna copïwch y fformiwla i'ch celloedd angenrheidiol, ac mae'r holl eiriau dyblyg wedi'u tynnu o bob cell. Gweler y screenshot:

doc-remove-duplicate-character-1

Nodyn: Yn y fformiwla uchod, A2 yn nodi'r gell rydych chi am ei defnyddio, ac mae'r coma (,) yn sefyll am y marc atalnodi sy'n gwahanu'r geiriau mewn cell, gallwch ei newid i unrhyw atalnodau eraill i'ch angen.

Offer Cynhyrchiant Swyddfa Gorau

Nodweddion Poblogaidd: Darganfod, Amlygu neu Adnabod Dyblygiadau   |  Dileu Rhesi Gwag   |  Cyfuno Colofnau neu Gelloedd heb Colli Data   |   Rownd heb Fformiwla ...
Super-edrych: VLookup Meini Prawf Lluosog    VLookup Gwerth Lluosog  |   VLookup Ar Draws Taflenni Lluosog   |   Edrych Niwlog ....
Rhestr gwympo Uwch: Creu Rhestr Gollwng yn Gyflym   |  Rhestr Gollwng Dibynnol   |  Rhestr Gollwng Aml-ddewis ....
Rheolwr Colofn: Ychwanegu Nifer Penodol o Golofnau  |  Symud Colofnau  |  Toglo Statws Gwelededd Colofnau Cudd  |  Cymharwch Ystodau a Cholofnau ...
Nodweddion dan Sylw: Ffocws ar y Grid   |  Golwg Dylunio   |   Bar Fformiwla Mawr    Rheolwr Llyfr Gwaith a Thaflen   |  Llyfrgell Adnoddau (Testun Auto)   |  Dewiswr Dyddiad   |  Cyfuno Taflenni Gwaith   |  Amgryptio/Dadgryptio Celloedd    Anfon E-byst trwy Restr   |  Hidlo Super   |   Hidlo Arbennig (hidlo mewn print trwm/italig/strikethrough...) ...
15 Set Offer Gorau12 Testun offer (Ychwanegu Testun, Dileu Cymeriadau,...)   |   50 + Siart Mathau (Siart Gantt,...)   |   40+ Ymarferol Fformiwlâu (Cyfrifwch oedran yn seiliedig ar ben-blwydd,...)   |   19 mewnosod offer (Mewnosod Cod QR, Mewnosod Llun o'r Llwybr,...)   |   12 Trosi offer (Rhifau i Eiriau, Trosi arian cyfred,...)   |   7 Uno a Hollti offer (Rhesi Cyfuno Uwch, Celloedd Hollt,...)   |   ... a mwy

Supercharge Eich Sgiliau Excel gyda Kutools ar gyfer Excel, a Phrofiad Effeithlonrwydd Fel Erioed Erioed. Kutools ar gyfer Excel Yn Cynnig Dros 300 o Nodweddion Uwch i Hybu Cynhyrchiant ac Arbed Amser.  Cliciwch Yma i Gael Y Nodwedd Sydd Ei Angen Y Mwyaf...

tab kte 201905


Mae Office Tab yn dod â rhyngwyneb Tabbed i Office, ac yn Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
How to remove duplicate words in excel template


Flooring|Tiles Flooring|Flooring|Vitrified Tiles|Tiles|Flooring|Tiles
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Thank you for the beautiful and useful coding. Can we do the same using Power Query?
This comment was minimized by the moderator on the site
Hi, is there a way to get the result of "RemoveDupes2" alphabetically sorted?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Hello, deezy,
The second method works well for numbers, please see the below screenshot.
Please try it again, thank you!
This comment was minimized by the moderator on the site
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.
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations