Potential Cell Character Limitation causing formula failure

[Techgique]

Hi Everyone,

Perhaps I don't understand the separation formula I using well enough, but everything seemed to be working nicely up until data was entered beyond a certain point in a cell. Overall, I am using data imported from another source that is separated by double asterisks between terms ("* *") and though all entries up to this point were separating just fine, once I crossed a threshold, it started failing. My initial thought is that it has to do with the repeat function portion that inserts space equal to the length of the cell, but sadly I don't know enough about the formula I am using to adjust it (if that is possible). I was able to recreate the problem in a single sheet in order to make it easy to see the issue. If anyone is able to shed some light on the limitation or how to adjust the formula to not fail after a certain number that would be awesome.

In the attached image, you can see that when the count is at 787 it works fine, but at 978 it fails using the exact same formula. Any help is appreciated, Thank you!

 

[Fluff]

For 365 how about

=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"* *","</m><m>")&"</m></k>","//m"))

and it will spill across

 

[Rick Rothstein]

Do you really need "live" formulas for this? If not, why don't you select the column, use the Replace dialog box to replace "* *" with "*" (without using the quote marks), then use the Text To Columns dialog box to split the data out into individual columns using the asterisk as the delimiter? Note that if you do this, the output column you specify with be blank because of the leading asterisks.

 

[Techgique]

For 365 how about

=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"* *","</m><m>")&"</m></k>","//m"))

and it will spill across

Thank you for the suggestion, however I am not using XML files (It's basically a bunch of terms that get crammed into a cell), and I am using the online version of 365, which according to Microsoft, FILTERXML is not an option.

 

[Techgique]

Do you really need "live" formulas for this? If not, why don't you select the column, use the Replace dialog box to replace "* *" with "*" (without using the quote marks), then use the Text To Columns dialog box to split the data out into individual columns using the asterisk as the delimiter? Note that if you do this, the output column you specify with be blank because of the leading asterisks.

There is a lot more going on behind this and everything worked great up until I found this weird limit (and I don't understand what is causing the limit, as it was my understanding that a cell can contain up to 32k characters), but I am trying to make this something that folks unfamiliar with excel can simply paste in the XLS output from another program into a sheet which then populates a sea of other sheets, charts, and dynamic graphs. What I would really like to know is what is causing this limit if anyone knows. Ultimately I am trying to remove as many steps as possible for other folks that may use this much larger workbook (which would be locked and hidden aside from the sheet where the xls data is pasted).

 

[Techgique]

There is a lot more going on behind this and everything worked great up until I found this weird limit (and I don't understand what is causing the limit, as it was my understanding that a cell can contain up to 32k characters), but I am trying to make this something that folks unfamiliar with excel can simply paste in the XLS output from another program into a sheet which then populates a sea of other sheets, charts, and dynamic graphs. What I would really like to know is what is causing this limit if anyone knows. Ultimately I am trying to remove as many steps as possible for other folks that may use this much larger workbook (which would be locked and hidden aside from the sheet where the xls data is pasted).

Also to expand on this Rick, the terms from other program exporting the xls file are surrounded by the asterisk in order to delineate between terms that are similar or have the same parts of terms like "Systems Engineer" "Engineer" "System of Systems Engineer", etc, so by surrounding it with asterisks it essentially creates the guaranteed separation of terms.

 

[Fluff]

Ok, how about

=TRANSPOSE(LET(Txt,"^"&SUBSTITUTE(A2,"* *","^")&"^",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,"^","")))-1),UNIQUE(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"^","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,"^","~",Qty)),""))))

 

[Techgique]

Ok, how about

=TRANSPOSE(LET(Txt,"^"&SUBSTITUTE(A2,"* *","^")&"^",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,"^","")))-1),UNIQUE(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"^","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,"^","~",Qty)),""))))

This looks to be functioning. Before I move into the real worksheet, is the reason for it stopping at a few cells because of the "UNIQUE" portion? In other words, this should work for all entries provided they are not repeats correct? (Which is totally fine as there are no repeats)

 

[Fluff]

Not quite sure why I put that in there, if you want all the entries use

=TRANSPOSE(LET(Txt,"^"&SUBSTITUTE(A2,"* *","^")&"^",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,"^","")))-1),REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"^","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,"^","~",Qty)),"")))

 

[Techgique]

Not quite sure why I put that in there, if you want all the entries use

=TRANSPOSE(LET(Txt,"^"&SUBSTITUTE(A2,"* *","^")&"^",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt,"^","")))-1),REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt,"^","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt,"^","~",Qty)),"")))

Thank you so much Fluff, this was perfect and completely solved my issue. I additionally confirmed that it fixed on my real workbook. The help is greatly appreciated! If anyone happens across this and knows the underlying reason for the failure of the original formula I would love to learn why, but for now Fluff's solution does exactly what I need.