Reversing formula breaks if input too long

I tidies up the first two formulae and then used this:

=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)),"")

copied across (to ZZZ in the file)

It's working to 292 columns and will work for more.

And the commas? Not needed??

Try it now.

Now, now. We don't expect posts to be bumped until at least 24 hr. There were a number of issues. Eventually, I chucked everything out and went back to basics. For fun, I was trying to teach myself FILTERXML yesterday. I have been in total awe of those who can use it proficiently. Your problem was tailor made for FILTERXML. So having failed at my first attempt and having failed to figure out what was wrong...

Try this, in C2, copied across (it's to ZZ) in the attached file:

=IFERROR(LET(S,"<A><B>"&SUBSTITUTE($B$2," ","</B><B>")&"</B></A>",L,LEN($B$2)-LEN(SUBSTITUTE($B$2," ",""))+1,FILTERXML(S,"//B["&L-(COLUMNS($C2:C2)-1)&"]")),"")

I think it's OK... but check it over... nucleotide sequences always leave me with spots in front of my eyes!!

Here is UDF Code

Please Login or Register  to view this content.

in D2
=ReverseWords(B2)
How to Use UDF code:
In Tthe developer tab click--> Visual Basic
VB window opens
Insert--> Module
Paste the code.
Close the window.
Now UDF is available in Function List

What exactly did you edit? I just afdded a few random nucleotides onto the end of A2 and it worked perfectly...

Please confirm that you ARE using Excel 365. When you launch Excel does it say in the rectangular green screen, as it starts up?

Here's a screenshot...

Administrative Note:

Welcome to the forum.

It has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

Since you are new here, I have done if for you THIS time. Feel free to cross post. Just don't keep it a secret.

https://www.mrexcel.com/board/thread...rmula.1157548/

Expect your thread to be blocked, until YOU provide the link, if you fail to declare cross-posts again. A lot of people (me included) get irritated if we have spent a lot of time working on a problem that you have already found a nice solution for elsewhere.

Originally Posted by EKrotz

All I'm editing is anything in A2. For example I deleted the first "A" in the sequence and it disappears. I'll upload my file. When I reopen this file it still doesn't work either.

Works fine over here!!

Here it is.... first A deleted.

Just a possibility... does your version of Excel support LET?

Type =LET( in a cell... does the function appear?

And just check FILTERXML, too, though since it's been around for a few years, that's not likely to be the problem.

OK. Bl@@dy microsoft!!

Here's a LET-free version. Check it and then I'll explain...

OK.... I have had O365 for about a week, and I'm trying to use its new functionality. So, on this occasion, I have to curb my enthusiasm. Instead of LET, which allows me to "give a formula a short name" I used Named Ranges instead (CTRL-F3 to view/edit)... So, I defined S (for string...) to add the XML code to your space-separated string in B2; and L (for length) as the number of nodes in the XML string, diminishing by 1 per column as the formula is dragged to the right (is the opposite of increments, excrements?? It feels that way today!!).

You ahev to be careful with setting up NRs. They have a tendence to wander.. to the last row or column, or insert multiple sets of " on a whim. So, when settinng them up I routinely check them TWICE, just to make sure that they have not wandered off to some far-flung part of your sheet.

How do you intend to use this sheet? One sequence at a time in A1, or separate sequences in A1, A2, etc. I may need to tweak the NRs if the latter option is your answer.

OK. The NRs have been tweaked and they SEEM to be OK. See file. It'll be fine for any number of rows in column A

You're welcome and thanks for the rep.