Potential Cell Character Limitation causing formula failure

Techgique

New Member
Joined
Apr 26, 2022
Messages
15
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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!
 

Attachments

  • TermSeparationIssue.PNG
    TermSeparationIssue.PNG
    126.5 KB · Views: 22

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
For 365 how about
Excel Formula:
=TRANSPOSE(FILTERXML("<k><m>"&SUBSTITUTE(A2,"* *","</m><m>")&"</m></k>","//m"))
and it will spill across
 
Upvote 0
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.
 
Upvote 0
For 365 how about
Excel Formula:
=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.
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
Ok, how about
Excel Formula:
=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)),""))))
 
Upvote 0
Ok, how about
Excel Formula:
=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)
 
Upvote 0
Not quite sure why I put that in there, if you want all the entries use
Excel Formula:
=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)),"")))
 
Upvote 0
Solution
Not quite sure why I put that in there, if you want all the entries use
Excel Formula:
=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.
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

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