Excel 2016 Text to columns 255 character limit

microsoft-excel

Your string, which when I copy/paste it, is only 281 characters. So I would expect it to split into two strings of 5 and 275 characters.

However, there is something strange about that string, and it seems to cause the old Text-to-Columns wizard to truncate it to 255 characters. At first I thought it might be related to the various Unicode characters ZWSP and ZWNJ scattered here and there. But replacing them with spaces still resulted in the same truncation.

Other ASCII text strings can be split on the comma even with lengths much greater than 255. Possibly a bug?

However, in Excel 2016, you can use the column splitting tool accessed from the Get & Transform tab on the Data ribbon. If you have updated your version (eg via Office 365) this may open when you double click on a CSV file. If it does not, open your file using the From Text/CSV selection of Get & Transform Data. Follow the prompts (it should show a Unicode type of file), and select comma for the column split. Doing that, it splits correctly.

Also, if you can import the csv row into a single row (unsplit) in Excel, you can use the From Table/Range option on Get & Transform to navigate to the column split command that will work.

EDIT: Further investigation into this reveals the bug seems to be triggered if the first character of the segment following the comma is a plus (+), minus (-) or equal (=) sign.

It turns out that another workaround for the bug is to specify that the column be classified as TEXT (step 3 of the wizard). This may be useful for those with older versions of Excel.

No, there is not. It is the limitation of the "column width" as specified in the spec: Excel 2016 limits

Your best bet would be to split the content prior to import using some type of script. i.e. python. Plenty of example on Stack overflow like:

https://stackoverflow.com/questions/7111068/split-string-by-count-of-characters

I have the same issue while exporting from SSIS ,it will automatically truncate with 255 character.

Root Cause

Excel automatically assign column length limit by considering first 8 consecutive rows.So if a column have more than 255 character then excel will took that length otherwise the default 255 will be allowed.