Custom List for Sort Order - Using Sorted Values w/ Commas

[Hawks18]

So, I've got a unique scenario that I can't figure out and doesn't seem to have been solved elsewhere yet.

Excel allows users to create "Custom List" data for sorting cells - unfortunately, these lists are inherently delimited via commas and it seems to be non-negotiable.

Ordinarily, it wouldn't cause an issue except that in this case the very list that I'd like to use for Sort Order has several values that are separated by commas...

Does anybody know how-to prevent Excel from delimiting Custom List values that are listed with commas? (w/o using VBA code)

Example List:

C
C, LW
C, RW
C, LW, RW
LW
LW, RW
RW
D
G

I'd appreciate any help on this subject. Thanks!

 

[Hawks18]

I've been trying to avoid VBA, as it requires file extension to change and I'd like to avoid functionality loss for end users on mobile or older versions of Excel. It's something that I supposed could be explored, though.

I appreciate all of the ideas! It certainly has helped me think through this issue and ponder the best solution.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-ge...-sort-order-using-sorted-values-w-commas.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Rick Rothstein]

...as I tried to simplify my original post for conciseness

That is almost always a bad idea as the best that can happen is you get a great answer to a problem you do not really have nor actually care about. This almost always means you will come back and explain your actual problem and ask whoever gave you that great answer to revise his/her solution for it. Do you see the problem here, not only from your standpoint (loss of time getting the answer you need), but from the volunteer's standpoint (you are telling him/her that he/she wasted their time developing the first solution, time that could have been spent helping someone else who was seeking help)?

 

[Hawks18]

Cross posted https://www.excelforum.com/excel-ge...-sort-order-using-sorted-values-w-commas.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Fair enough. Thanks for the heads-up, Fluff...

 

[Hawks18]

That is almost always a bad idea as the best that can happen is you get a great answer to a problem you do not really have nor actually care about. This almost always means you will come back and explain your actual problem and ask whoever gave you that great answer to revise his/her solution for it. Do you see the problem here, not only from your standpoint (loss of time getting the answer you need), but from the volunteer's standpoint (you are telling him/her that he/she wasted their time developing the first solution, time that could have been spent helping someone else who was seeking help)?

Sure. Except, often it can lead to a dreaded "TL:DR" scenario...I'd detailed the problem in its entirety, but limited the daisy-chain / spider-web nature of table references, if that makes sense.

In fairness, "Does anybody know how-to prevent Excel from delimiting Custom List values that are listed with commas? (w/o using VBA code)" was my original request...and despite everyone's helpful intentions, each response has failed to address that specific issue, but rather offer workaround solutions involving significantly more effort.

Could you imagine the solution possibilities had my original post been overly complex and confusing?

I'm not complaining, as I truly appreciate everyone's ideas...from my perspective, less is more sometimes as it limits confusion and often narrows results. I don't see it as a problem in this particular instance, as I came into this assuming there wasn't an actual proper solution given Excel's limitations and outside-the-box thinking (or throwing darts, perhaps?) may ultimately lead to something reasonable that I hadn't considered.

 

[mikerickson]

In fairness, "Does anybody know how-to prevent Excel from delimiting Custom List values that are listed with commas? (w/o using VBA code)" was my original request...and despite everyone's helpful intentions, each response has failed to address that specific issue, but rather offer workaround solutions involving significantly more effort....

Well, to answer your question directly
No. Excel's Custom List is a comma delimited list, which prevents commas from being a character in any of the list items.
Unless you want to put effort into a work-around, Custom List sorting won't work for your situation.

 

[Hawks18]

Well, to answer your question directly
No. Excel's Custom List is a comma delimited list, which prevents commas from being a character in any of the list items.
Unless you want to put effort into a work-around, Custom List sorting won't work for your situation.

Fair enough. I guess it comes down to which workaround best suits my needs.