Reverse order delimited text formula

[EKrotz]

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.

Accurate Synthesis Calculator WIP.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Step 1 - Input Sequences	Sequence Output	Reversed sequence output	Reverse Delimited Sequence
2	rA*rGrCAG*C*TrACrC	,rna*,rna,rna,DNA,DNA*,DNA*,DNA,rna,DNA,rna	rna,DNA,rna,DNA,DNA*,DNA*,DNA,rna,rna,rna*	rna	DNA	rna	DNA	DNA*	DNA*	DNA	rna	rna	rna*
3	AG*CT*	DNA DNA* DNA DNA*	DNA* DNA DNA* DNA	DNA*	DNA	DNA*	DNA
4	rArGrCrU	rna rna rna rna	rna rna rna rna	rna	rna	rna	rna
5	rA*rG*rC*rU*AGCT	rna* rna* rna* rna* DNA DNA DNA DNA	DNA DNA DNA DNA rna* rna* rna* rna*	DNA	DNA	DNA	DNA	rna*	rna*	rna*	rna*
Normal, Thio, thRNA, thOMe

Cell Formulas
Range	Formula
B2	B2	=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	C2	=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:O2	D2	=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:B5	B3	=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:C5	C3	=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:O3	D3	=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:O4	D4	=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:O5	D5	=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)),"")

 

[Fluff]

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.

 

[jasonb75]

See if this method works any better, this does the delimited reverse sequence first, then joins it together in column C.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
7	rA*rGrCAG*C*TrACrC	,rna*,rna,rna,DNA,DNA*,DNA*,DNA,rna,DNA,rna	rna,DNA,rna,DNA,DNA*,DNA*,DNA,rna,rna,rna*	rna	DNA	rna	DNA	DNA*	DNA*	DNA	rna	rna	rna*
Sheet1

Cell Formulas
Range	Formula
B7	B7	=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"),"",))
C7	C7	=TEXTJOIN(",",1,D7:O7)
D7:O7	D7	=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.

 

[Peter_SSs]

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.
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.

EKrotz.xlsm
	A	B	C
1	Step 1 - Input Sequences	Sequence Output	Reversed sequence output
2	rA*rGrCAG*C*TrACrC	,rna*,rna,rna,DNA,DNA*,DNA*,DNA,rna,DNA,rna	rna,DNA,rna,DNA,DNA*,DNA*,DNA,rna,rna,rna*
3	AG*CT*	,DNA,DNA*,DNA,DNA*	DNA*,DNA,DNA*,DNA
4	rArGrCrU	,rna,rna,rna,rna	rna,rna,rna,rna
5	rA*rG*rC*rU*AGCT	,rna*,rna*,rna*,rna*,DNA,DNA,DNA,DNA	DNA,DNA,DNA,DNA,rna*,rna*,rna*,rna*
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=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:C5	C2	=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)),"")))

 

[jasonb75]

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.

 

[Peter_SSs]

it appears that our efforts have been wasted anyway,

Not wasted for me - I enjoyed the exercise and the chance to practice with LET and the dynamic array functions.

 

[jasonb75]

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