ExcelTip: Text to Columns and Concatenate – two best friends

If you often work with lists, especially those containing names, then you have probably dealt with having to format the data in columns.

If you have ever needed to compare lists, then you probably have needed to rearrange the content in columns.

For example, FirstName LastName, versus LastName, FirstName.

How do you handle making the switch?

Excel makes it (somewhat) easy, with the Text to Columns feature, followed by the Concatenate formula.

You can find Text to Columns under the DATA ribbon. There are a number of settings you can choose from, and I tend to use delimited, and space or comma, depending on what I need.

Once you have your data split into columns, check the rows to make sure you don’t have an extra column with information, which often happens when you have either middle initials, or a middle name without a hyphen.

To combine the columns back into one with the format you need, use concatenate.  Assuming that we start in row 2, your FirstName is in column A, and your LastName is in Column B, the following formula will combine the information as LastName, FirstName.


Note that there are commas between each section, and quotes surround the text in the middle. It might be a little difficult to see:  the quotes have a comma followed by a space between them.

What the formula is doing is concatenating, or joining, the information in cell B2, then adding a comma followed by a space, and then adding the information in cell A2.

To learn more about these functions, see

What does Sway do?

I was asked this question recently and best I can tell, Sway is like an online presentation system. Unlike PowerPoint, where you need a specific piece of software to play the presentation, Sway handles it all online for you.

Sway uses a card system, much like story boarding a movie, and you can embed all kinds of media in there including YouTube videos, Flickr photos, Google maps, and more. People make newsletters with it, or tutorials.

In doing my research, I came across Microsoft’s Sway YouTube Channel, and I posted the link in the learning resource site as well. I hope this helps you in your discovery of this new way of sharing information.

 The downside of Sway is that it doesn’t generate a file. You can’t take your presentation and store it on a server. It lives on a website. Another downside is that, while you can make some basic edits, you can’t really brand a sway with custom fonts or colors.

Fixing the titles on wiki pages

SharePoint Online has a bit of a glitch when it comes to creating wiki pages. It doesn’t allow us to change the Title without also changing the aspx file name. If you include spaces, then you end up with %20 in the file name (such as file%20name.aspx), which makes the URL tough to read.

Our guru at work whipped up some specialized javascript code for me today that lets me use underscores in the file name, and then his javascript strips it out. Pretty cool. The code he gave me is shown below

< script type="text/javascript" language="javascript" >
var title_replacement_target = document.getElementById("DeltaPlaceHolderPageTitleInTitleArea").getElementsByTagName('a')[0];
title_replacement_target.innerHTML = title_replacement_target.innerHTML.replace(/_/g, " ");
 < /script >

Just remove the spaces from the < and the >

Breaking SharePoint Online

I had been meaning to write a post about how easy it is to break SharePoint, and while that sounds scary, it isn’t quite as bad as it might sound.

First, you need to know that I learned a new concept while I was in Baltimore for training. I would exclaim at several occasions “SharePoint is not doing what it’s supposed to” and Guy, the instructor, would calmly explain that “SharePoint is doing exactly what it’s supposed to; it’s just not doing what you expect it to do.”

Same difference, right?

I thought I would use this opportunity to share a few things that don’t work out OOTB (out of the box), or where additional features would be welcomed.

How to break a list or library

Heed this one – this is real! If you do this, there is no way back from it other than to start over with a clean list or library.

One way to break your list or library is to click on the link for the actual list or library and then to go to the gear and choose Edit Page. Do not do this!

I don’t know why, and Guy in Baltimore doesn’t either, and neither does Microsoft by the way, but when you use the Edit Page function on a list or library page (perhaps to add some explanatory text to it), it actually renders the list itself somewhat useless, and you lose the LIST and LIBRARY tabs at the top of the page, which means you cannot change the settings.

What SharePoint has done is turned your list into a page with a list web part on it. You can still work with your list, but you have to click on the web part in order to make those changes. It’s not a fun experience, and as of this writing, this is not reversible.

What should you do instead?

Create a page from within the sitepages application, and then add the list or library web part to that. It will allow you to write your explanatory text on that page without breaking the functionality of the actual list.

Links list

Wouldn’t it be nice to have links lists allow the option to have links open in a new tab? You can choose to allow links to open in a new window within pages and within discussions, but for the links list, no such option exists.

Another feature that doesn’t work OOTB is the ability to insert links from one site into another. It sort of works, but you have to customize something in the web part settings. There will be a tutorial on this in the future.

Document Library

You would think that if you have the capability of giving a document a title other than file name ID2277_KS_2015, that you would have the capability to show that title and have it link to the file. Another example of a feature we would like to see out of the box. The workaround for this is to create a new hyperlink field with the Document Title and the URL of the file, and then attaching a workflow to populate that field. Again, there will be a tutorial on this in the future.

While we are on the subject of document library, wouldn’t it be nice if we could add the capability of adding a Link to a Document without it breaking the default options for creating a new document? I resolved that one with the creation of DSA-specific templates, but I don’t understand why they couldn’t just allow us to add a feature without breaking other features.

Discussion Boards

Discussion boards don’t let you filter discussions right out of the box. Also, the manage discussions view is broken – it links to replies to discussions, not the actual discussions.

I discovered this glitch when working on DSA Home, and I wanted to filter Aspirations for Student Learning and Keystone discussions on the ASL/Keystone page. It took me a while to learn the workaround, and to fix it you actually need to go into the source code for that feature, so if you need to customize your discussion board views, read this article to activate that hidden lever.

Let’s delve into Delve

o365-delveDelve is one of the products that comes with Office365. It acts as a kind of dashboard for you and your organization. You can search people, see what files they’re working on (if they have made them available to you), and it’s where you update your profile so that your coworkers can learn more about you.

While I have been in Delve several times over the past 5 months, it didn’t really click for me what use it would be for me, until I had my “ah-ha” moment.

Here are two introductory articles from Microsoft to help explain what Delve is and what the bells and whistles do:

Delve is where your SharePoint profile lives

When you click on the “Me” link, you will see Activity and Profile. When you click on Profile, that is where you tell us a little bit about yourself and where you upload your photo. I recommend you fill in as much as you are willing to share, mostly about work, and if you want to tell us about your kids or pets, go for it – just know that everyone in your tenant can see what you write in your profile.

Delve is where you can Skype your coworkers

Next to your picture there is a message button. Have you ever tried to click on it? It does nothing…. or does it?

When I clicked on it on the Mac, nothing happened. However, on a PC, if you have Skype for Business installed, that little message button will open Skype and get you started with a message to the recipient.

Delve shows you the latest files you’ve been working on… and where those files live.

Now here is where the power of Delve lies in my opinion. I regularly work in about 5-6 different sites. Most of my work is SharePoint related, but where does the file live? It could be anywhere, on the learning site, the blog site, the SPOCK site, the ICC site, my admin site, the CIT site, or on my OneDrive. And as we make open/unrestricted/public versions of sites, files could quite literally be anywhere.

Luckily Delve knows where

The Me dashboard, under Activity, shows the files I work on the most, or the files I worked on most recently. the “See more related” box could be helpful, or not, depending on what it thinks I want to see, but all the “content cards” give me an overview of the files I work on, and it tells me where they live.

From here, I can click on the content card itself and open the file I am looking at. I can also click on the folder or site name, and Delve will take me to the folder or document library where the file exists.

The use of boards

Using boards is a little bit like tagging. Each content card (file overview) has a place to “add to a board” – you type in where you want to save it. You can tag your own files, and you can also tag files that other people have shared with me. In my OneDrive for example, I have a document that includes instructions for uploading images to ShowOff, one of the systems managed by CIT’s web team. I tagged that document webteam so that when I look for documents pertaining to work on the webteam, I will find it there.

You can create as many boards as you want, and you can share those boards with other people.

Privacy is still maintained

Any documents that you have not shared with anyone else, even if they appear on your board, will remain private only to you. Even if someone can see my webteam board, unless I have shared that ShowOff document with the team, they are unable to see it.

Cleaning up my inbox!

I had been meaning to implement my RunRulesNow macro for several weeks now and just hadn’t gotten around to it. Today I said, enough is enough! Let’s get this thing implemented.

Thanks to the great folks at VBOffice.net there wasn’t a lot that I had to do, except modify each of my rules to have the exception of “when flagged for Any action”.

So to give a bit of context: I like a clean inbox, and I don’t particularly enjoy filing, so what I had done in the past, is have a bunch of rules, and then I had this cool macro, and it would file all my read emails into various folders. It worked superbly well, and unfortunately I had not saved the macro anywhere so I had to look for it again. I found it at http://www.vboffice.net/en/developers/run-rules-now

Because I want my rules to run against read emails only, and leave unread emails in my inbox, I had to disable most of my rules. Some rules I kept on, such as those for newsletters and whatnot, because I don’t need them cluttering up my inbox from the get go.

This macro allows me to run the rules against the inbox (or other folders if I so choose), keeps all my flagged items in the inbox (that’s set in the individual rules), and leaves all my unread emails in the inbox as well. It allows me to keep all my open loops in the inbox and remove the clutter from prior emails without having to manually check each box. If you want to know more about this, just add a comment in the reply section and I’ll try to help. Thanks again to the folks on VBOffice.net for doing all the hard work for me. I appreciate it!

Beware the changing page title

When you change the title of a page, a couple of things happen. As noted in yesterday’s post, the title of the page is the same as the file name, which means that changing the title of a page also changes the file name.

Links to your page could break

Since SharePoint is a database, changing the name of the file often results in SharePoint correcting the address for you, since it doesn’t care what you call a page; it knows it as 00ZKJSD9A8E09JKD9D01 or something like that, and it will change its internal pointer accordingly.

Where it does matter, is if anyone inserts a link to a page and keeps the https://######.sharepoint.com in front of the URL because then it’s not a link within the database, but an actual address. Changing the title of the page may then in fact break the links in other places, so think carefully before you do that. Also think about links from the outside. I recently had this happen where someone changed the title of a page, but had not realized that the link was active from a non-SharePoint site.

Images can get lost

Images uploaded to a page, live within a folder by the same name as the page name. Changing the page name doesn’t move existing images to a new folder, but if you add another image to the page after you have changed the page name, then SharePoint will create a new folder with the new page name, and now you have two folders supporting the same page. Just something to think about as you plan out your site.