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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi, borrowing some ability from @FLuffs previous post to you in July, I came up with the following:

Book1
ABCDEFG
1String AString BSolution ASolution Bmy solution Amy solution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT3-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-9DDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),INDEX(textstring,1,s)&"-",""),INDEX(textstring,1,COLUMNS(textstring))))
F2:F4F2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),MID(B2,s,1),""),RIGHT(B2)))


Hope it helps.

cheers
Rob
 
Upvote 0
RobP said:
Hi, borrowing some ability from @FLuffs previous post to you in July, I came up with the following:

Book1
ABCDEFG
1String AString BSolution ASolution Bmy solution Amy solution B
27-6-1-3-4-2-2DDDDDTT7-6-1-3-4-2DDDDDT7-6-1-3-4-2DDDDDT
33-3-0-2-0-0DDTDTT3-0-2-0DTDT3-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-9DDDDDDDDDDDDDT6-8-13-11-10-14-7-4-7-2-1-0-3-9DDDDDDDDDDDDDT
Sheet2
Cell Formulas
RangeFormula
E2:E4E2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),INDEX(textstring,1,s)&"-",""),INDEX(textstring,1,COLUMNS(textstring))))
F2:F4F2=LET(textstring,TEXTSPLIT(A2,"-"),s,TRANSPOSE(SEQUENCE(COLUMNS(textstring)-1)),CONCAT(IF(INDEX(textstring,1,s)<>INDEX(textstring,1,s+1),MID(B2,s,1),""),RIGHT(B2)))


Hope it helps.

cheers
Rob
Click to expand...
Hi Rob, thanks for the answer.

I'm not sure if I'm missing something, but when I try to apply the formula I get the error "The first argument of LET must be a valid name". Do you have a clue about what's happening?

Best,
alecambo
 
Upvote 0
Ciao Ale,

ah.. shoot. Its the TEXTSPLIT() function. I forgot its still in Beta testing (I have it on O365, but its not available on your office 2021 version).
I will see if there is another way I can change it.

Sorry
Rob
 
Upvote 0
RobP said:
ah.. shoot. Its the TEXTSPLIT() function.
Click to expand...
I don't think that not having TEXTSPLIT should produce that particular error message.

If TEXTSPLIT was available, what about these?

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(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))))
D2:D4D2=LET(ts,TEXTSPLIT(A2,"-"),s,SEQUENCE(COLUMNS(ts)-1),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))
 
Upvote 0
Peter_SSs said:
I don't think that not having TEXTSPLIT should produce that particular error message.

If TEXTSPLIT was available, what about these?

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(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))))
D2:D4D2=LET(ts,TEXTSPLIT(A2,"-"),s,SEQUENCE(COLUMNS(ts)-1),CONCAT(LEFT(B2,1),IF(INDEX(ts,s+1)=INDEX(ts,s),"",MID(B2,s+1,1))))
Click to expand...
Hi, thanks for the answer.

I'm still getting the same error message.

Best,
alecambo
 
Upvote 0
As I imagined (but agree with you Peter in that its a little odd)

@Peter_SSs - would you happen to know, that if something is in "Beta", whether it gets rolled out to O2021 at some point, or would it only appear in O365 versions ?

Rob
 
Upvote 0
Those new functions are no longer on Beta, they have been rolled out to 365 users on the Month (Current) channel & the semi-annual channel will probably get them in Jan.
However they will never be available on xl 2021
 
Upvote 0
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))))
 
Upvote 0
Solution
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
More Efficient Looping
2
Replies
19
Views
244
bhsoundman
B
A
  • Solved
VBA Case Error
Replies
2
Views
65
Ark68
A
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
318
etaf
etaf
V
  • Question Question
Extract dd.mm.yyyy string from a text and convert it to dd.mm.yyyy date format
Replies
2
Views
80
Sergius
S
D
  • Question Question
Compile error: Wrong number of arguments or invalid property assignment
Replies
2
Views
74
daveyc18
D

Forum statistics

Threads
1,224,815
Messages
6,181,136
Members
453,021
Latest member
Justyna P

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