How to remove duplicate characters VBA and Formula

I am a Excel learner, sometimes when i need a help solution for my question, not just to get the job done, but also to learn it, so next time similar work, i would not have to ask again.

i have mixed char string in cells like this  22wwrr4455//ee88%%77119955119944

here i want to remove all duplicates that comes in consecutive manner for example from the above string i want the result 2wr45/e8%719955194  

i would be grateful if someone can share idea, on how to do this with built in excel formula, or if formula is not possible at all, then UDF

Flora EdwardsMedicineAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rodney EndrigaData AnalystCommented:
In Visual Basic Editor, click Insert > Module, and paste the following code in the Module Window:

Function RemoveDupeCHARS(rText As Range) As String

Dim xValue As String, xChar As String, strFinal As String
Dim xDict as Object
Set xDict = CreateObject("Scripting.Dictionary")

xValue = rText.Value

For i = 1 To VBA.Len(xValue)
	xChar = VBA.Mid(xValue, i, 1)
	If xDict.Exists(xChar) Then
		xDict(xChar) = ""
		strFinal = strFinal & xChar
	End If
RemoveDupeCHARS = strFinal

End Function

Open in new window

This UDF will provide you a UNIQUE text string, with no duplicate characters in the string.

In your Worksheet, use this formula in Cell B1: =RemoveDupeCHARS(A1) if your text is in Cell A1, adjust cell reference according to your data.
Please insert the following code into a VBA module.

Function RemoveDuplicateConsecutiveChars(rCells As Range) As String
  Dim sValue As String, sPos1 As String, sReturn As String
  Dim i As Integer, vCounter As Integer, vFound As Integer
  Dim aChars(99, 1)

  sValue = rCells.Value
  vCounter = 1

  For i = 1 To Len(sValue)
    sPos1 = Mid(sValue, i, 1)
    vFound = 0
    If aChars(vCounter - 1, 0) = sPos1 Then
      vFound = 1
    End If
    If vFound = 0 Then
      aChars(vCounter, 0) = sPos1
      aChars(vCounter, 1) = 0
      vCounter = vCounter + 1
    End If
  For i = 1 To 99
    If aChars(i, 1) = 0 Then
      sReturn = sReturn & aChars(i, 0)
      Exit For
    End If

  RemoveDuplicateConsecutiveChars = sReturn
End Function

Open in new window

If the data is in cell A1 then use this formula in B1.
Why does "9955" not get reduced to "95" in your result string?
This function uses a regular expression object to clean the string.
Public Function CleanDups(parmString As String) As String
    Static oRE As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "(.)\1"
    End If
    If oRE.test(parmString) Then
        CleanDups = oRE.Replace(parmString, "$1")
        CleanDups = parmString
    End If
End Function

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Flora EdwardsMedicineAuthor Commented:
Aikimark provided most effecient solution, short in code and did the job.

Zalazar  UDF worked too

Rodney's UDF did not work.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.