How to remove duplicates in a string

Question

I have a file contains 38,000 records each row contains 2 or more ';' at the end. is there any formula to remove the end repeated ';' in Excel or any other tool for example

Use a regular expression replacement. Replace ;+$ with ; — Barmar
– Barmar, Commented Nov 13, 2018 at 0:50

ƬƦƖƝƛ · Accepted Answer · 2018-11-13 01:33:14Z

1

To remove repeated characters _{^{(semi-colons in this case)}}

Hit CTRL+H
Find What: ;; (two semicolons)
Replace with: ; (one semicolon)
Click Replace All. When it finishes, repeat Step 4 until there are no more matches found.

Now the document will have no more than one semicolon in a row.

Remove repeated characters using a VBA function:

The following function does the same thing using VBA, and for any character you choose:

Function removeDoubleChars(txt As String, doubleChar As String) As String
'removes all multiple-consecutive [doubleChar] within [txt]
    Do
        txt = Replace(txt, doubleChar & doubleChar, doubleChar)
    Loop While InStr(txt, doubleChar & doubleChar) > 0
    removeDoubleChars = txt
End Function

You would use this like Range("A1") = removeDoubleChars ( Range("A1"), ";") to remove consecutive semicolons from cell A1.

edited Nov 13, 2018 at 1:33

answered Nov 13, 2018 at 1:23

ƬƦƖƝƛ

1791 silver badge11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to remove duplicates in a string

1 Answer 1

To remove repeated characters _{^{(semi-colons in this case)}}

Remove repeated characters using a VBA function:

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

To remove repeated characters (semi-colons in this case)

Remove repeated characters using a VBA function:

Comments

Your Answer

Sign up or log in

Post as a guest

Related

To remove repeated characters _{^{(semi-colons in this case)}}