Remove first duplicate characters from a cell

alecambo

New Member
Joined
Apr 10, 2015
Messages
35
Office Version
  1. 2021
Platform
  1. Windows
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
RGYYOYJFKSELMRGYYOYJFKSELM


Thanks in advance,
alecambo
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Here is one way to do it...
Excel Formula:
=LEFT(A2)&MID(A2,FIND(LEFT(TRIM(SUBSTITUTE(A2,LEFT(A2)," "))),A2),LEN(A2))
 
Upvote 0
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.

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

alecambo.xlsm
ABC
1Object2ResultResult
2CCCJJGLLQCJJGLLQCJJGLLQ
3AAAAAUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQAUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQAUUPPOSZYLGHMERRLFJIOWWKAYAYHQQRQ
4LLXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWVLXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWVLXAAIVJJOOPKRWWVIALXLYGEGCZSMAATFFOJPIVWVGXXLCCGZESWWV
5RGYYOYJFKSELMRGYYOYJFKSELMRGYYOYJFKSELM
6CCCCC
Sheet1
Cell Formulas
RangeFormula
B2:B6B2=LEFT(A2)&MID(A2,FIND(LEFT(TRIM(SUBSTITUTE(A2&1,LEFT(A2)," "))),A2&1),LEN(A2))
C2:C6C2=Dedupe(A2)
 
Upvote 0
Solution
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.

VBA Code:
Function Dedupe(s As String) As String
  With CreateObject("VBScript.RegExp")
    .Pattern = "(^)(.)(\2*)"
    Dedupe = .Replace(s, "$2")
  End With
End Function
 
Last edited:
Upvote 0
For those VBA'ers out there who might be interested, here is how I would write the UDF for this question...
VBA Code:
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
 
Upvote 0
.. or that concept could be abbreviated to

VBA Code:
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
 
Upvote 0
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.

VBA Code:
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 :)
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,707
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top