Remove adjacent duplicate numbers from String A and remove characters in the same position from String B.

A

alecambo

New Member
Joined
Apr 10, 2015
Messages
35
Office Version
  1. 2021
Platform
  1. Windows
Hello,

I have two strings of letters (columns String A and String B) and I want to transform them into strings like the ones in Solution 1 and Solution 2 correspondingly.

String AString BSolution ASolution B
7-6-1-3-4-2-2DDDDDTT
7-6-1-3-4-2
DDDDDT
3-3-0-2-0-0
DDTDTT
3-0-2-0DTDT
6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9
DDDDDDDDDDDDDT

In the case of String A, I need to remove any adjacent duplicate numbers (the numbers are separated by a "-"), so the string 6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9 would turn into 6-8-13-11-10-14-7-4-7-2-1-0-3-9.
String B is a little bit more complicated: both String A and String B have the same amount of items, and I want to transform String 2 depending on the transformation of String 1 into Solution 1. For example, in my data, I have 6-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9 as String 1 and DDDDDDDDDDDDDDDDT as String 2. Solution 1 would be 6-8-13-11-10-14-7-4-7-2-1-0-3-9 (remove adjacent duplicate numbers) and Solution 2 would be DDDDDDDDDDDDDT (remove characters that are in the same position as the numbers removed from String 1).

I hope this makes sense and I'm happy to provide any further clarification.

Best,
alecambo
 
Sort by date Sort by votes
Peter_SSs said:
See what happens with this one.

22 09 29.xlsm
ABCD
1String AString BSolution ASolution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT
46-8-8-13-13-11-10-14-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Remove Text
Cell Formulas
RangeFormula
C2:C4C2=LET(n,LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),s,SEQUENCE(n),ss,SEQUENCE(n+1),ts,TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),ss*100-99,100)),TEXTJOIN("-",1,INDEX(ts,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",INDEX(ts,s+1))))
D2:D4D2=LET(n,LEN(A2)-LEN(SUBSTITUTE(A2,"-","")),s,SEQUENCE(n),ss,SEQUENCE(n+1),ts,TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",100)),ss*100-99,100)),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))
Click to expand...
Sorry for the late reply.

I'm still getting the same error message.

Best,
alecambo
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
veyselemre said:
VBA Code: 
Sub test()

    For i = 2 To Cells(Rows.Count, 1).End(3).Row
   
        bol = Split(Cells(i, 1).Value, "-")
        yeni = Mid(Cells(i, 2).Value, 1, 1)
        For ii = 1 To UBound(bol)
            If bol(ii - 1) = bol(ii) Then
                ekle = ""
                bol(ii) = ""
            Else
                ekle = Mid(Cells(i, 2).Value, ii + 1, 1)
            End If
            yeni = yeni & ekle
        Next ii
        Cells(i, 5).Value = Replace(WorksheetFunction.Trim(Join(bol, " ")), " ", "-")
        Cells(i, 6).Value = yeni
   
    Next i
End Sub
Click to expand...
Thanks for the reply. Your code works but it only collapses double characters (9-9 to 9) but it does not work for triple characters (9-9-9 to 9-9)
 
Upvote 0
alecambo said:
I'm still getting the same error message.
Click to expand...
Could you be using a regional Excel version that uses ; instead of , as the function argument separator?

What happens if you try each of these for the Solution A question?
Excel Formula: 
=LET(ts;TEXTSPLIT(A2;"-");s;SEQUENCE(COLUMNS(ts)-1);TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))
Excel Formula: 
=LET(n;LEN(A2)-LEN(SUBSTITUTE(A2;"-";""));s;SEQUENCE(n);ss;SEQUENCE(n+1);ts;TRIM(MID(SUBSTITUTE(A2;"-";REPT(" ";100));ss*100-99;100));TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))
 
Upvote 0
Upvote 0
RobP said:
Hi Pete, I think this is not the case - as @Fluff previous code here: Remove adjacent duplicate characters from String A and remove characters in the same position from String B. worked just find for him.
Click to expand...
Yes, I had seen some of the OP's previous threads where that did not seem to be the issue. However, so far, the only way that I have been able to reproduce the particular error reported by the OP is to change commas to semicolons in my version so though the question was worth asking.
 
Upvote 0
Peter_SSs said:
Could you be using a regional Excel version that uses ; instead of , as the function argument separator?

What happens if you try each of these for the Solution A question?
Excel Formula: 
=LET(ts;TEXTSPLIT(A2;"-");s;SEQUENCE(COLUMNS(ts)-1);TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))
Excel Formula: 
=LET(n;LEN(A2)-LEN(SUBSTITUTE(A2;"-";""));s;SEQUENCE(n);ss;SEQUENCE(n+1);ts;TRIM(MID(SUBSTITUTE(A2;"-";REPT(" ";100));ss*100-99;100));TEXTJOIN("-";1;INDEX(ts;1);IF(INDEX(ts;s+1)=INDEX(ts;s);"";INDEX(ts;s+1))))
Click to expand...
Hi, thanks for your reply.

The first solution gives me a #NAME? error.
The second solution seems to work perfectly fine :)

I didn't miss the post right before veyselemre's VBA, I also tried it and got the "The first argument of LET must be a valid name" message.

Best,
alecambo
 
Upvote 0
alecambo said:
The second solution seems to work perfectly fine :)
Click to expand...
Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.
 
Upvote 0
alecambo said:
The second solution seems to work perfectly fine :)
Click to expand...
Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.
 
Upvote 0
... would be interested to know if Fluffs previous code is still working for you (on those items that do not have the "-" between them though ?

Rob
 
Upvote 0
Peter_SSs said:
Great news! So you should be able to use both the suggestions from post #9 if you swap all the commas for semicolons.

.. and you need to remember that swap for any future suggestions in the forum - that mostly will use commas.
Click to expand...
Thanks! I've already marked the post as the solution ;)
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Remove Duplicates with Dynamic Columns Array
Replies
0
Views
298
trough
T
C
  • Solved
Transposing Horizontal String
Replies
6
Views
120
c0087
C
P
  • Question
vba at each change
2
Replies
15
Views
684
kevin9999
K
J
  • Solved
IFS
Replies
4
Views
100
Peter_SSs
Peter_SSs
N
  • Question
Need Formula help
Replies
5
Views
358
AhoyNC
A

Forum statistics

Threads
1,215,009
Messages
6,122,674
Members
449,091
Latest member
peppernaut

Share this page

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