Excel & SharePoint

Objective

I needed to prepare an Excel file for importing into SharePoint.

Excel

• First, I need an Excel formula to populate a short name (255 characters) from a much longer name ( found in "col").
• The length of the short name is constrained by SharePoint's "single line of text" field.
• The challenge is to find acronyms within col or if am acronym can't be found, to truncate col to 255 characters.
• Example: 

GNU is Not Unix (GNU) => GNU
This is another name => This is another name

• This is the formula to use. 

= IF(ISNUMBER(FIND("(";[col]));MID([col];FIND("(";[col]) + 1; FIND(")";[col]) - FIND("(";[col])-1);LEFT(CLEAN(TRIM([col]));255))

Importing into SharePoint

The only error free way to do this is to use Excel to export into SharePoint.

• Create an Excel table and select it
• Go to "table tools" tab and choose the "export table to SharePoint list" option within "export".