Remove first duplicate characters from a cell

[alecambo]

Hello,

Is there a way to collapse the first repeated characters of a string into a single character? My current data looks like the one in column Object2 and I want to transform it so it looks like the one in Result.
In other words, if a string inside a cell starts with 3 'Cs', I want the result to be the same string but starting with only one 'C' instead of three. If the cell starts with a character that doesn't repeat, it should stay as such. My cells have a variable number of repeated first characters (from 2 to n). I hope this is clear enough, if not, I'll provide further info.

Object2	Result
CCCJJGLLQ	CJJGLLQ
AAAAAUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ	AUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ
LLXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV	LXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV
RGYYOYJFKSELM	RGYYOYJFKSELM

Thanks in advance,
alecambo

 

[Rick Rothstein]

Here is one way to do it...

=LEFT(A2)&MID(A2,FIND(LEFT(TRIM(SUBSTITUTE(A2,LEFT(A2)," "))),A2),LEN(A2))

 

[Peter_SSs]

Assuming the data is all letters, I would suggest a slight modification to Rick's formula if data like row 6 below is possible.

A user-defined function like this might also be used.

Function Dedupe(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^)(.)(\2+)(.*)"
    Dedupe = .Replace(s, "$2$4")
  End With
End Function

alecambo.xlsm
	A	B	C
1	Object2	Result	Result
2	CCCJJGLLQ	CJJGLLQ	CJJGLLQ
3	AAAAAUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ	AUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ	AUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ
4	LLXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV	LXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV	LXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV
5	RGYYOYJFKSELM	RGYYOYJFKSELM	RGYYOYJFKSELM
6	CCC	C	C
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=LEFT(A2)&MID(A2,FIND(LEFT(TRIM(SUBSTITUTE(A2&1,LEFT(A2)," "))),A2&1),LEN(A2))
C2:C6	C2	=Dedupe(A2)

 

[alecambo]

Thanks for your responses, it worked perfectly!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also just realised that the udf had a tiny bit more in it than was needed. I think this should do the same job.

Function Dedupe(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^)(.)(\2*)"
    Dedupe = .Replace(s, "$2")
  End With
End Function

 

[Rick Rothstein]

For those VBA'ers out there who might be interested, here is how I would write the UDF for this question...

Function DeDupe(S As String) As String
  Dim X As Long
  For X = 1 To Len(S)
    If Mid(S, X, 1) <> Mid(S, X + 1, 1) Then
      DeDupe = Mid(S, X)
      Exit For
    End If
  Next
End Function

 

[Peter_SSs]

.. or that concept could be abbreviated to

Function Dedupe(s As String) As String
  Dim X As Long
  Do Until Mid(s, X + 1, 1) <> Mid(s, X + 2, 1)
    X = X + 1
  Loop
  Dedupe = Mid(s, X + 1)
End Function

 

[alecambo]

You're welcome. Glad we could help. Thanks for the follow-up.

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also just realised that the udf had a tiny bit more in it than was needed. I think this should do the same job.

Function Dedupe(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^)(.)(\2*)"
    Dedupe = .Replace(s, "$2")
  End With
End Function

Thanks for the tip regarding my Excel version, I just did so

 

[Peter_SSs]

.. tip regarding my Excel version, I just did so

Thanks for doing that.