Custom List Length Limit

[caj1980]

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>

 

[Eric W]

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.

 

[caj1980]

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!

 

[Kyosti]

The complete list imported for me when I recreated the issue. Are you selecting all of the data cells when you import?

 

[caj1980]

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"

 

[caj1980]

@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...

 

[caj1980]

Moved the Find/Replace challenge to a new thread here:

https://www.mrexcel.com/forum/excel-questions/1020477-auto-find-replace-multiples.html#post4896912