MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom List Limits


May 28, 2017 - by Bill Jelen

Custom List Limits

I love custom lists in Excel. They are great for the fill handle and for sorting data into a different sequence. Custom Lists should allow 254 items. But for some reason, a reader is encountering a situation where Excel is only keeping the first 38 items! We will get to the bottom of this mystery.

Watch Video

  • 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()) and Ctrl + Shift + Enter and LEN(TEXTJOIN(",",True,Range))
  • Workaround with ABS for sorting in this particular case for Don
  • But the best workaround… the thing that Don needs to do:

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2098 custom list limits this is just
  • a bizarre one Don F using mac 2011 right
  • so we're not even using the real version
  • of Excel we're using the fake version of
  • Excel trying to sort by a custom list
  • but it's only accepting the first 38
  • items in the list and I know that's
  • wrong because Excel can handle up to 254
  • items in the list or at least that's
  • what I thought all right and Dawn has
  • player name number of wins and then
  • margin like how far off from the score
  • were they so the proper sequence is the
  • perfect score is zero and then over by
  • one under by one and then over by two
  • under by two over by three under by
  • three and so on and Don is trying to
  • sort the margin column by this custom
  • list now I've never tried that but it
  • should be easy to do so over here proper
  • sequence zero 1 and then the formulas
  • going to be minus the value just before
  • us and then equal the number two above
  • plus 1 right now that I have those two
  • formulas I should be able to cruise down
  • throw 201 too far there but that's okay
  • and we should have the exact sequence
  • that we need down to 99 and minus 99 so
  • there's our perfect set of answers I'm
  • going to copy that so ctrl C to copy and
  • then paste as values paste those values
  • like that all right so I should be able
  • to set up a custom list that will handle
  • this right no problem so we go to file
  • options advanced scroll 83% of the way
  • down choose edit custom lists and we
  • will import that list what cells without
  • simple text were ignored you're not
  • allowed to have a custom list full of
  • numbers but Don says this is working for
  • the first 38 what's up with that well
  • everything about this for awhile I
  • realized that Don must not have been
  • trying to import he must have just been
  • typing those numbers into the dialog box
  • so here's what I do I'm going to do ctrl
  • C to copy all of them I'm gonna go to
  • notepad and paste in notepad like this
  • control V and then select everything
  • edit
  • select all and control-c come back to
  • excel file options advanced 83% of the
  • way down edit custom lists and I'm going
  • to type that list in here like it's
  • control V alright and that all works but
  • we click Add the maximum length for
  • custom list has been exceeded only the
  • first 255 characters will be saved and
  • when you look at this sure house they
  • are creating a custom list that only
  • goes down into 38 - 38 39 and then BAM
  • the last three right so this is so
  • bizarre they're actually letting me
  • create a custom list with numbers but
  • they're not allowing me to get 255 I
  • mean it works it works and then if we
  • actually try and sort over here so we'll
  • say data sort and sort on this custom
  • list the one that only goes to 39 click
  • OK click ok well if it's in the list it
  • sorts correctly alright so the positive
  • sixes show before the minus 6 but then
  • once we get down to anything beyond 39
  • it's just going to sort in the sequences
  • that's not in the list so it's going
  • from smallest to largest here so
  • somebody missed by 67 points is better
  • than somebody missed by positive 42
  • points is it is just completely screwing
  • alright and what's up with this only
  • going to 38 now sure there's every other
  • number so you know I guess it's going to
  • down into the 30s we're getting down to
  • like there right are there one of those
  • too so what is that that is a total of
  • 78 items and hey I know that you're
  • allowed to to have 250 forums because I
  • talk about customers all the time of my
  • seminars all right you can have you know
  • 254 mzk let me show you
  • so item space 1 of course we can use the
  • filling idle for that I'll drag down to
  • 254 like that now those aren't formulas
  • so we should be able to be able to do
  • file options
  • advance going on to edit custom lists
  • and we will import that list all right
  • there it is BAM no problems no error
  • message everything is great everything
  • is good
  • it's not great only goes to item 234 we
  • I know you can have 254 why is it
  • stopping at 234 that's bizarre that's
  • bizarre what's up with that so here we
  • know that it's only going down to item
  • 234 right there okay now when we were
  • typing the items in the list there's
  • some number of characters there was the
  • limit so I wonder if there's some number
  • of characters that's the limit here
  • equals some of the length of that whole
  • bunch of stuff press control shift enter
  • and it's 1764 characters 234 items and I
  • know that you can have 254 I've done
  • this before and let's try something
  • crazier all right let's try this let's
  • try instead of item let's try something
  • longer so 10 characters of space and the
  • number 1 will go down 254 rows and we'll
  • try and import this list so file options
  • advanced edit custom lists we'll import
  • this list no error message seems like it
  • worked but it only goes down to 140 what
  • the heck is up with that what is the
  • limit I thought it could be 254 so let's
  • see how many characters we have if we
  • get down to 140 all right so let's leave
  • everything else after this and in fact
  • I'll come over here to this formula and
  • copy the exact same formula where all
  • right no at this point I'm pretty
  • exasperated with the excel team what's
  • up here 1764 and here 1852 hey Microsoft
  • what is the limit exactly what is the
  • limit ah but here's the thing
  • they must be storing this as a series of
  • delimited strings
  • all right so they're taking all the
  • items and then they're they're adding a
  • comma after each one alright so here
  • since we have office 365 office 365 we
  • can use the new text joint so equal text
  • join of all of those with a comment
  • between I don't know if it's really a
  • comma or not ignore emptying true comma
  • and these items so we get that and
  • actually I just want to know the link to
  • that whole thing so the length is 1997
  • and when I do the same thing over here
  • 1991 oh so clearly the limit must be
  • 2000 characters including a invisible
  • comma between each item this is all
  • pretty bizarre alright so I always
  • thought it was 254 times it's not 254
  • times is 254 items provided is less than
  • 2,000 characters provided the items
  • aren't too long all right so just to
  • test my theory let's just use a space
  • one like that and we'll grab the fill
  • handle and drag these should be really
  • nice and short because and we'll go down
  • to 255 254 let's let's go to 255 - to
  • test it alright so now with this if I
  • ask for the length of the text join
  • 14:21 there should be no Pramod also
  • select the whole thing and file options
  • advanced scroll all the way down to the
  • bottom edit custom lists click import
  • all right and turn off all the way down
  • to 254 all right so it's it's 254 items
  • provided is less than 2,000 characters
  • including a invisible comma after each
  • item is how this works you know so but
  • back to down from there
  • it sure is annoying that the dialog box
  • if we just go in and start typing things
  • in the dialog box instead of having a
  • 2,000 character element it has a 255
  • character all right so don has no way to
  • type this thing and and when we try and
  • import numbers it refuses to import the
  • numbers it says no deal anything that's
  • not plain text is not going to work
  • right so the one thing I suggested to
  • Don is an alternate solution
  • I say hey let's just come out here and
  • add a helper column and this helper
  • column is going to be the absolute value
  • of that number all right and we'll
  • double click to shoot that down and then
  • we're going to do is you're just going
  • to sort descending by the absolute value
  • a setting by the absolute value all
  • right and then the 4 6 and then minus 6
  • all right these are all just get sorted
  • together you know so it's not bad I
  • guess what you could really do is you
  • could sort by the helper and then add a
  • level and then sort by the margin
  • descending largest to smallest click
  • okay and that'll get what Don is looking
  • for so all of the positive sixes show up
  • before the minus six and then eight and
  • then minus eleven all right you know but
  • this is a hassle like hey Microsoft why
  • do we have to go to all this hassle why
  • would you let us type 2,000 characters
  • into the dialog box or even better yet
  • since it apparently works to have
  • numbers in the custom list I mean it is
  • working here why won't you let us import
  • it alright now here is the workaround
  • and I'm sorry that took this freaking
  • long to get to the end but this is what
  • I had to go through
  • all this out so I'm going to add this
  • workaround column all right and this
  • workaround column is going to be the
  • text the text of that number in quotes
  • comma our quote zero quote close paren
  • like that and we're going to copy that
  • down all right so now we're taking these
  • numbers here see the numbers are right
  • justified and we are left justifying it
  • by using the tax function unfortunately
  • though you can't use formulas in the
  • custom list if I would come here to file
  • options advanced go down to edit custom
  • lists and we'll import that no house is
  • that simple texts were ignored okay fine
  • getting really annoyed now right now
  • what I'm going to do is I want to select
  • the workaround column I'm going to use
  • the Home tab I'm going to copy it and
  • then I'm going to paste and paste values
  • if you don't do this step it's not going
  • to work and here's how to prove to
  • yourself that it works all right if
  • those are really numbers when you do the
  • sum function it's going to give you an
  • answer like this that's going to be five
  • all right but if I do the sum of the
  • work around column I'm going to get zero
  • all right and the reason is is the sum
  • can't some text all right so these are
  • real numbers these are real texts you
  • have to get it to be real text once you
  • have it as real text select the whole
  • thing go to file options advanced edit
  • custom lists we'll get rid of this old
  • custom list here that we don't need
  • we'll get rid of this list that we don't
  • need we'll get rid of this list and we
  • don't need we'll even get rid of this
  • list that only goes down to 38 and 39
  • and then finally we'll import the whole
  • list and this whole list goes from 0 all
  • the way down to 99 and minus 99 and
  • presumably store it as text
  • all right but here's the cool awesome
  • amazing thing let's just come back up
  • and we'll sort this data by name data a
  • disease now everything is just
  • completely random there and then we will
  • sort by margin and the order is going to
  • be a custom list the custom list is
  • going to be this sequence click OK and
  • click OK and it works the four sixes the
  • minus 68 minus eleven and so on now the
  • beautiful thing about this is once you
  • get it set up all right it's a hassle to
  • put these numbers over here in the off
  • to the side change them to text copy
  • paste special values and then import
  • that list but the beautiful thing is
  • once that's imported then forevermore on
  • this computer we're going to be able to
  • sort correctly by margin so Don jeez I
  • think that it's worth it to go through
  • that whole thing 617 Excel mystery
  • solved now this is a new mystery why
  • this custom list wouldn't work but this
  • book is full of stuff like this video
  • just the bizarre they arcane and so on
  • click that on the top right hand corner
  • to buy the book alright done what's the
  • sort by custom list of numeric will work
  • it seems to work well it seems to work
  • for the first 38 numbers but you can't
  • import numeric cells into the custom
  • list dialog box ah
  • so try and type the custom numbers or
  • actually I guess I pasted the notepad
  • and then from notepad I pasted but then
  • there's this crazy 255 character limit
  • when typing bet that must be left over
  • from Excel 2003 right because the limit
  • is more than that now you can have 254
  • items is it 254 times 254 times but less
  • than 2000 characters when you have a
  • visible column comma between each item
  • now we did some math here text math with
  • equals sum of the length of neutral
  • shift-enter and then the length of the
  • text join if you have office 365 and are
  • on the latest version even came up with
  • this workaround with the absolute value
  • for sorting which will help in this
  • particular case for done but I think
  • what Don needs to do the best work
  • around this is what I tell them to do on
  • the YouTube video put those numbers 0 1
  • minus 1 and a 1 to a 200 and then in the
  • column next to that equal text of a one
  • quote 0 quote close paren that's going
  • to convert it to text copy that formula
  • all the way down and then select that
  • range of formulas copy and
  • and after you copy go to the paste
  • drop-down so copy paste and paste as
  • values and if you're successful when you
  • do the sum have that whole thing is
  • going to sum to a number that's not ours
  • going to sum to zero it's going to sum
  • to zero be careful don't choose all of
  • your numbers because your numbers do
  • happen to sum to zero now the B contains
  • sex numbers you can import because of
  • custom lists well there you go Don one
  • of the crazier questions I've ever heard
  • in Excel and we've uncovered at least
  • two well definitely one bug and excelled
  • at the custom list dialog box won't
  • allow more than 255 characters and then
  • this bizarre thing that the apparently
  • will sort a custom list by numbers but
  • they won't let you import numbers
  • alright so I'm going to call that bug
  • number two and then this work around
  • here all right so hey I want to thank
  • Don for sending the question in and if
  • you hum around this long I wanna thank
  • you for stuffing bottles you next time
  • for another net cast from MrExcel

Download File

Download the sample file here: Podcast2098.xlsm

Title Photo: getbrett / Pixabay