Excel - Custom List Limits - Episode 2098 

Microsoft Excel Tutorial: Sorting by a Custom List That Contains Numbers.
You can sort by a custom list, but are you allowed to sort numbers by a custom list? Apparently... Yes. But it requires some workarounds.
Custom Lists can be 254 items but the total length of the items (including a theoretical column between each item) is 2000 characters.
Don wants to sort by a custom list that is numeric!
Will this work? It seems to work!
But you can not import numeric cells to the custom list dialog box.
So, try to type numbers into the Custom List dialog box…. You are struck with a foolish 255 character limit when typing.
WTH is the limit? 254 items? Aha - 254 items, but less than 2000 characters when you add the invisible comma between each item
Did some text math with =SUM(LEN()) +CtrlShift+Enter and LEN(TEXTJOIN(",",True,Range))
Workaround with ABS for sorting in this particular case for Don.
Workaround:
Put the correct sequence in cells A1:A200
Formula in B1 is =TEXT(A1,"0")
Copy the formula from B1 to B2:B200
Select B1:B200
Copy
Paste Values
Select B1:B200
File, Options, Advanced, Edit Custom Lists, Import, OK, OK
You can now sort by your column F using the Custom List.
#excel
#microsoft
#microsoftexcel
#excelbug
#excelchallenge
#evergreen
#excelsort
#excelcustomlist
This video answers these common search terms:
what is custom list in excel
how to use custom lists in excel
how do you custom a list on excel
how to add a custom list to excel;
how to apply custom lists in excel
how to make a custom list in excel
how to make custom sort list in excel
how to create a custom sorting list in excel
how to create a custom autofill list from values in excel
how to create a custom autofill list in excel
how long can the custom list be in excel
limits on custom lists in excel
Table of Contents
(00:00) Excel Custom List Limits
(01:29) Import a Custom List to Excel
(02:26) Typing a Custom List but the Limit is lower
(04:27) Importing a custom list with 250 items works
(05:11) There is another limit based on sum of LEN
(07:16) Limit is 2000 characters with invisible commas
(08:39) Limit is 254 items if length is short
(09:35) Workaround: Sold by Absolute Value
(10:35) Workaround 2: Storing Text Numbers in Custom List
(12:24) Import Custom List from Text Numbers
(12:44) Sort by a Custom List in Excel
(13:27) Buy the Book
(13:45) Episode Recap
(13:55) Clicking Like really helps the algorithm
Join the MrExcel Message Board discussion about this video at www.mrexcel.com/board/threads...