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


Comments

Post a Comment

Popular posts from this blog

Understanding ITIL Service Management the UML way…

-
Image
I originally had a great deal of difficult reconciling the differences amongst ITIL Change Management, Incident Management, and Problem Management. Having researched on the subjects and understanding them better now, I’ve tried to put the concepts together using a UML class diagram to capture my understanding and to keep it imprinted. Similarly, I wanted to differentiate the various categories of changes (Standard, Emergency, Normal) using an easy to understand & self-explanatory manner – a UML Activity Diagram!
Read more

How to depict (Professional-Looking) Logical Network Diagrams in Astah

-
Image
I have always had to create (professional-looking) UML diagrams using Visio-like icons. You’ve got to admit that showing a bunch of boring UML boxes hardly interests anyone less technical (like anyone whose designation has the words “manager” or “director”). This is actually possible in Astah! I’ve created a template for future projects that allows me to produce logical network diagrams that look like the following: To create this diagram, you need to do the following: Assign various icons to stereotypes. I created the following for my use: Create a new UML Deployment Diagram Every element in the diagram is created as a Node The nodes are subsequently assigned the appropriate <<stereotype>>. E.g. Internet is <<internet>>, WebZone FW is <<firewall>>, Web LB is <<loadBalancer>> Ensure the “Icon Notation” is set to “Customized Icon” for each node Instead of the boring boxes, the corresponding icons automatically gets displayed!
Read more