Reverse order delimited text formula

EKrotz

New Member
Joined
Jan 4, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Strap in, this is a weird one. I'm making a calculator of sorts and am having trouble with one of the formulas. Specifically the 'reverse sequence output' in C2.

To get you on board with what I'm trying to do, I'll explain a little bit. I want to be able to post a a sequence in A2 in that type of text format. In B2 I need to make some substitutions to make time calculations down the line easier, but I have all that figured out and they aren't included below. I need to make those substitutions because for example 'A' takes the same time to synth as 'G', but 'rA' takes a different time than 'A' and 'G'. The presence of an asterisk next to one of those means it will take even more time.

To complicate things the most, the sequences are made from right to left and four sequences can go at the same time, so I need to match up the right most DNA or rna for each sequence, moving leftward. Everything I have pasted below is an attempt to make some substitutions, and then turn the sequence around and delimit it. Everything is working perfectly, except for some reason, the 'reverse sequence output' formula breaks down if you extent the sequences to an arbitrary amount it seems, generlly higher like in the 70s to 100s. If you'd like to see how, copy and paste the input in A2 to multiply it about ten times.

There is something wrong with =TEXTJOIN(",",,TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",LEN(B2))),SEQUENCE(,LEN(B2)-LEN(SUBSTITUTE(B2,",","")),LEN(B2)*(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))),-LEN(B2)),LEN(B2)))) that makes it struggle with longer sequences. For the record, the longest I will need to go will be 120 rna and/or DNAs in a row.

You'll notice there is a difference between B2 and B3:B5. I was experimenting with different substitution styles. I do not care if it works with or without commas. Also note that the formulas in column D are extended out 119 more columns to accommodate all the delimits, they could not be fit here.

Any help is appreciated, or if you see an entirely better way to do what I'm doing please let me know.

Cell Formulas
RangeFormula
B2B2=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"rA",",rna"),"rG",",rna"),"rC",",rna"),"rU",",rna"),"A",",DNA"),"G",",DNA"),"C",",DNA"),"T",",DNA"),"",))
C2C2=TEXTJOIN(",",,TRIM(MID(SUBSTITUTE($B2,",",REPT(" ",LEN(B2))),SEQUENCE(,LEN(B2)-LEN(SUBSTITUTE(B2,",","")),LEN(B2)*(LEN(B2)-LEN(SUBSTITUTE(B2,",",""))),-LEN(B2)),LEN(B2))))
D2:O2D2=IFERROR(MID(TRIM(SUBSTITUTE($C$2,","," ")),(SUMPRODUCT(LEN($C$2:C2)*1)-LEN($C$2)+1),FIND(" ",TRIM(SUBSTITUTE($C$2,","," "))&" ",SUMPRODUCT(LEN($C$2:C2)*1)-LEN($C$2)+2)-(SUMPRODUCT(LEN($C$2:C2)*1)-LEN($C$2)+1)),"")
B3:B5B3=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"rA"," rna"),"rG"," rna"),"rC"," rna"),"rU"," rna"),"A"," DNA"),"G"," DNA"),"C"," DNA"),"T"," DNA"),"",))
C3:C5C3=TEXTJOIN(" ",,TRIM(MID(SUBSTITUTE(B3," ",REPT(" ",99)),((LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1)-ROW($XFD$1:INDEX(XFC:XFC,LEN(B3)-LEN(SUBSTITUTE(B3," ",""))+1)))*99+1,99)))
D3:O3D3=IFERROR(MID(TRIM(SUBSTITUTE($C$3,","," ")),(SUMPRODUCT(LEN($C$3:C3)*1)-LEN($C$3)+1),FIND(" ",TRIM(SUBSTITUTE($C$3,","," "))&" ",SUMPRODUCT(LEN($C$3:C3)*1)-LEN($C$3)+2)-(SUMPRODUCT(LEN($C$3:C3)*1)-LEN($C$3)+1)),"")
D4:O4D4=IFERROR(MID(TRIM(SUBSTITUTE($C$4,","," ")),(SUMPRODUCT(LEN($C$4:C4)*1)-LEN($C$4)+1),FIND(" ",TRIM(SUBSTITUTE($C$4,","," "))&" ",SUMPRODUCT(LEN($C$4:C4)*1)-LEN($C$4)+2)-(SUMPRODUCT(LEN($C$4:C4)*1)-LEN($C$4)+1)),"")
D5:O5D5=IFERROR(MID(TRIM(SUBSTITUTE($C$5,","," ")),(SUMPRODUCT(LEN($C$5:C5)*1)-LEN($C$5)+1),FIND(" ",TRIM(SUBSTITUTE($C$5,","," "))&" ",SUMPRODUCT(LEN($C$5:C5)*1)-LEN($C$5)+2)-(SUMPRODUCT(LEN($C$5:C5)*1)-LEN($C$5)+1)),"")
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Reversing formula breaks if input too long
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
See if this method works any better, this does the delimited reverse sequence first, then joins it together in column C.
Cell Formulas
RangeFormula
B7B7=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A7,"rA",",rna"),"rG",",rna"),"rC",",rna"),"rU",",rna"),"A",",DNA"),"G",",DNA"),"C",",DNA"),"T",",DNA"),"",))
C7C7=TEXTJOIN(",",1,D7:O7)
D7:O7D7=IFERROR(LET(strlen,LEN($B7)-LEN(SUBSTITUTE($B7,",",""))-COLUMNS($D7:D7)+1,newstr,SUBSTITUTE($B7&",",",","|",strlen),start,FIND("|",newstr)+1,MID(newstr,start,FIND(",",newstr,start)-start)),"")


**edited reply. Changed to work with original formula in column B.
 
Last edited:
Upvote 0
Welcome to the MrExcel board!

In post 1 I am not understanding why in column B, row 2 does the various substitutions with a comma prefix but the other rows are substituting with a space prefix. :confused:
Assuming that they could all be done the same (I have used the comma version) then the reversal of column B could done directly without the helper columns like this with no likelihood of breaking the 32,767 character limit of TEXTJOIN.

Cell Formulas
RangeFormula
B2:B5B2=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"rA",",rna"),"rG",",rna"),"rC",",rna"),"rU",",rna"),"A",",DNA"),"G",",DNA"),"C",",DNA"),"T",",DNA"),"",))
C2:C5C2=LET(s,B2&",",num,LEN(s)-LEN(SUBSTITUTE(s,",",""))-1,seq,SEQUENCE(,num,num,-1), TEXTJOIN(",",1,REPLACE(LEFT(s,FIND("#",SUBSTITUTE(s,",","#",seq+1))-1),1,FIND("#",SUBSTITUTE(s,",","#",seq)),"")))
 
Upvote 0
row 2 does the various substitutions with a comma prefix but the other rows are substituting with a space prefix.
That was the OP trying different methods to see if it fixed the error, Peter. It was mentioned at the end of post 1.
I'm not sure if columns D:? should be classed as helpers or if they are the actual desired output, if you look at post 1 you will see that they are extracted from column C rather than forming it, they just appear as helpers in my post because of the way that I used them.

Regardless of what is (or rather was) required, it appears that our efforts have been wasted anyway, I didn't notice until too late that it had already been resolved at the cross post thread. Looking at what was suggested there, it also appears that the OP has not yet received the update with the LET function.
 
Upvote 0
I enjoyed the exercise and the chance to practice with LET and the dynamic array functions.
That's a valid point, Peter :)
I got carried away with my effort and couldn't work out why my first formula had the same output in column C as the input in column B, I thought that I was using the functions wrong and it was doing nothing, then I realised that I reversed it twice :oops:
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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