While importing a csv into excel there is a column of data that occasionally has more than 255 characters in it. When running the text to column command, it truncates these cells to 255 characters. Is there way to get around this in excel 2016?
Excel 2016 Text to columns 255 character limit
microsoft excel
Related Question
- Microsoft Excel – Increase CSV Data Import Size Limit
- Excel – 255 Max character size in Excel cell when cell data is linked/fetch from external data source
- Excel – Numbers in scientific notation imported to Excel 2016 as text
- Excel – How to set the default data type to “text” while importing data from a text file in Excel
Best Answer
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
ZWSPandZWNJscattered 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 & Transformtab on theDataribbon. 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 theFrom Text/CSVselection ofGet & 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/Rangeoption onGet & Transformto 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.