Page 2

Page 2 of 19

Excel Tips…Using LEFT, MID and FIND to return initials from a name

If you have someone’s name in a cell and want to automatically extract initials, you can use LEFT, MID and FIND.

As always when working with text cells, you can wrap with UPPER, LOWER or PROPER.

A B < B function >
1 Joe Bloggs JB =LEFT(A1)&MID(A1,FIND(” “,A1)+1,1)
2 joe bloggs JB =UPPER(LEFT(A2)&MID(A2,FIND(” “,A2)+1,1))


I also found online  a funtion to manage three names – seems long so I imagine there is an easier way to do it, but it works:

A B < B function >
1 Joe John Bloggs JJB =IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=0,LEFT(A1,1),IF(LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))=1,LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1),LEFT(A1,1)&MID(A1,FIND(” “,A1)+1,1)&MID(A1,FIND(” “,A1,FIND(” “,A1)+1)+1,1)))


SharePoint Tips…How to colour code Calendar items


As you know SharePoint calendars are views of a list type containing columns including date and time.

You can add a choice category column of your choosing to your list and by using multiple views (overlays) use this to colour code entries in a calendar view.

The example below uses a filter called Task Status with options New, Started, Complete to be displayed with separate colours.

Category name and filter options are of course customisable.

Create Calendar

Create your calendar in the usual way

  • Browse to SharePoint site
  • Select Add an App
  • Choose Calendar, name it and click Create

Add Category Column item

  • Browse to the new calendar
  • From the menu ribbon select CALENDAR and click List Settings button
  • Click Create column
  • Name as Task Status, select type as Choice (menu to choose from), enter choices New, Started, Complete and select Drop-Down Menu or Radio buttons (you don’t want to allow multiple choices, so don’t use Checkboxes), and click OK

Create Calendar views

  • Return to the Calendar view, and from the menu ribbon select CALENDAR and click Create view button
  • Select Calendar View, name New, add a Filter option selecting Task Status and is equal to New and click OK
  • Repeat and create additional views named and filtered for Started and Complete
  • Create a new view called All Tasks, set to it Default View and use is equal to filter against Task Status but leave filter option blank. (This creates a ‘clear’ default view – this is to replace the default ‘Calendar view’ which would show duplicate values if used)

Create Calendar Overlay Views

  • From the menu ribbon select CALENDAR and click Calendar Overlays button
  • Click New Calendar

  • Add name New, select required colour from the Color drop-down
  • Click Resolve new to the Web URL field to load the site and confirm (or select) the appropriate Calendar in the List menu
  • Select New from List View, click Always Show and click OK

  • Repeat for Started and Complete then click OK to save the Calendar Overlays

The calendar default view will now display the three views overlaid showing each category as a different colour.

Excel Tips…Overview of PivotTables


The ExcelIsFun YouTube Channel is a great resource for learning Microsoft Excel and they’ve recently uploaded a fantastic video that explains how to setup and use PivotTables.

I’ve embedded the video below but I would recommend you visit and bookmark their channel as you’ll find loads of really useful videos and answers to most Excel questions you may have.

ExcelIsFun PivotTables & Slicers Made Easy!


« Older posts Newer posts »

© 2021

Theme by Anders NorénUp ↑