Thursday, September 24, 2009

More on Excel Cell Size Limitation

Excel 2003 can store up to 1024 characters in a cell. However, is shows only the first 256 characters so one may think the rest has been lost. If you look at the top input area you can see the entire string but not in the cell itself or upon printing. Excel 2007 doesn't have this issue and can correctly handle veryyyyyy long strings.

When you copy to other programs the target cell may have the same limitation and will cut the string, as explained in a previous article pertaining SharePoint Link type column.

Saturday, September 19, 2009

Copying long URLs from Excel to a SharePoint list

In another post I explained how to extract the URL string that has an associated friendly name from a link stored in a cell . The problem is that a simple Copy and Paste carries both the URL and the friendly name while Paste Special as Values carries the name only.

However, if you have long URLs you may have some issues. Although Excel cells can store very long strings, SharePoint Hyperlink or Picture column type can handle 256 characters so longer URLs will be cut when you copy from Excel to SharePoint.

To work around this limitation, you can copy from Excel to a Sharepoint Multiple lines of text column type with Rich text attribute. The link will have the full length and will also function as a link as one can click it to navigate to another page.

Since the URL is very long, it is not practical to use it without a friendly name. But unlike the Hyperlink column type where you could copy a URL with a friendly name from Excel to SharePoint, this won't work with the Multiple lines column type. You will have to work in a standard view rather than in a data sheet mode and type in friendly names one by one.