Custom List Length Limit

caj1980

Board Regular
Joined
Oct 23, 2013
Messages
108
I have searched all over the internet for a solution to this but have so far been unsuccessful. Lots of folks with similar issues out there but not exactly the issue I am having.

I am trying to import a custom list that contains 54 entries. I have the list typed into excel with each list entry in consecutive rows within a single column. When in the Custom List editor box, I use the import feature and select the list from the worksheet and click import. It only imports the first 35 rows not the full 54 rows. Is there a workaround to fix this?

Here is my custom list if you want to recreate:

Project Initiation#Pause#Provide Inputs at Project Kick-off
Project Initiation#Pause#Research Impact Assessment
Project Initiation#Pause#Verify Approved Cutsheet
Project Initiation#Pause#Hard Reserve Position
Project Initiation#In-Progress#Provide Inputs at Project Kick-off
Project Initiation#In-Progress#Research Impact Assessment
Project Initiation#In-Progress#Verify Approved Cutsheet
Project Initiation#In-Progress#Hard Reserve Position
Cabling Vendor Engagement#Pause#Create Vendor SOW
Cabling Vendor Engagement#Pause#Assist in Vendor Walk-Thru
Cabling Vendor Engagement#Pause#Analyze Bids from Vendors
Cabling Vendor Engagement#Pause#Vendor Selection and Support
Cabling Vendor Engagement#In-Progress#Create Vendor SOW
Cabling Vendor Engagement#In-Progress#Assist in Vendor Walk-Thru
Cabling Vendor Engagement#In-Progress#Analyze Bids from Vendors
Cabling Vendor Engagement#In-Progress#Vendor Selection and Support
Financial Vendor Approval#Pause#Cut TT to NSI Finance for PR and PO
Financial Vendor Approval#Pause#Approved PO to Vendor
Financial Vendor Approval#In-Progress#Cut TT to NSI Finance for PO
Financial Vendor Approval#In-Progress#Approved PO to Vendor
Cabling Installation#Pause#Cabling - CM and Access Request
Cabling Installation#Pause#Verify Material Procurement
Cabling Installation#Pause#Monitor Vendor Cable Installation
Cabling Installation#In-Progress#Cabling - CM and Access Request
Cabling Installation#In-Progress#Verify Material Procurement
Cabling Installation#In-Progress#Monitor Vendor Cable Installation
Rack Strider Workflow#Pause#Confirm Rack Arrival
Rack Strider Workflow#Pause#Final RSPC Validation
Rack Strider Workflow#Pause#Scan Rack Into Position
Rack Strider Workflow#Pause#Cut TT for Bolt Down & Energize
Rack Strider Workflow#Pause#Complete Strider Workflow
Rack Strider Workflow#In-Progress#Confirm Rack Arrival
Rack Strider Workflow#In-Progress#Final RSPC Validation
Rack Strider Workflow#In-Progress#Scan Rack Into Position
Rack Strider Workflow#In-Progress#Cut TT for Bolt Down & Energize
Rack Strider Workflow#In-Progress#Complete Strider Workflow
Build & Troubleshoot#Pause
Build & Troubleshoot#In-Progress
Validation and Turn-Up#Pause#Final Validation
Validation and Turn-Up#Pause#Turn-Up and NHO
Validation and Turn-Up#In-Progress#Final Validation
Validation and Turn-Up#In-Progress#Turn-Up and NHO
Project Closure#Pause#Create Project Closure CM
Project Closure#Pause#Rack Elevation & Take Pictures
Project Closure#Pause#Walk-Thru with DCO
Project Closure#Pause#NSI Manager sign-off
Project Closure#Pause#Close Project Closure CM and Project
Project Closure#In-Progress#Create Project Closure CM
Project Closure#In-Progress#Rack Elevation & Take Pictures
Project Closure#In-Progress#Walk-Thru with DCO
Project Closure#In-Progress#NSI Manager sign-off
Project Closure#In-Progress#Close Project Closure CM and Project
Project Done

<colgroup><col style="mso-width-source:userset;mso-width-alt:17700;width:363pt" width="484"> </colgroup><tbody>
[TD="width: 484"]Ready[/TD]

</tbody>
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I copied your list, and duplicated your problem. I tried to search and see if I could find an Excel limit that would explain it, like here:

Excel specifications and limits - Excel

I couldn't find anything that specifically addressed it. However, I created another list of 55 entries, and imported that, and it worked fine. The difference being that my entries were all 3 characters long. I surmised that there might be a total character limit, so I created a function that counted the number of characters used so far. Up to item 35, it was 1944. Item 36 took it up to 2009. Continuing my experiment, I shorted item 36 to 6 characters, and it was accepted. So it appears that there's an upper limit to the total number of characters, somewhere between 1950 and 2000 total.

If you're using this list for sorting, you might be able to work around it. If you truncate each of those items at the shortest point where it becomes unique, they might still sort in the right order, and you might be able to get the total character count under the limit. (I haven't tested this, but I think it's worth a shot.)

Hope this helps some.
 
Last edited:
Upvote 0
WOW! Great research and thanks for the input. That certainly makes sense although the ~2000 character limit seems random!

The only problem with truncating my list labels is that I would also then have to truncate them in the source data feeding the Pivot or the sort won't work since the two groups of data won't match any more. Maybe a Find/Replace macro would do the trick for the source data then the custom list would match.... hmm that's a crazy macro to record!

Thanks so much for the help!
 
Upvote 0
The complete list imported for me when I recreated the issue. Are you selecting all of the data cells when you import?
 
Upvote 0
The complete list imported for me when I recreated the issue. Are you selecting all of the data cells when you import?

Yes, I confirmed I selected all rows in the list. It behaves like it imports, but if you check the actual data in the custom list it is truncated after the 35 entry. Easy verification is the last word in the list should be "Done"
 
Upvote 0
@Eric W.

I truncated my list entries and brought the LEN from 2843 to 617 and it imports properly now into a custom list. Thanks for that suggestion!

Now to figure out an efficient way to convert the full labels to the truncated labels in my source data...
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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