628 – Text handling in Excel

Posted on April 29, 2022 (Updated April 28, 2022) by ewand

Anyone who has delved into writing formulæ in Excel will probably have had to manipulate strings of text at some point, possibly to clean up formatting or to convert what Excel thinks is a simple block of text into more meaningful data that we know it to be, like a number or a date.

There are simple ways of bulk handling text without resorting to writing a formula – copy all the names from the To: line in an Outlook email, for example: paste into a new spreadsheet and you’ll end up with a single line of text containing all of the display names and email addresses in one cell, which you may want to split up, to be of much use.

Separate the text into multiple columns by selecting the first cell, then go to the Data tab and look for Text to Columns, which presents a fairly powerful if somewhat old-fashioned looking dialog box, to step through fixing up your text.

In the example above, we have a “;” separating – or “delimiting” – each address, so we’ll use that to split the text across multiple columns.

Since we might want to create a table of names / addresses, select the cells spread across the columns, copy or cut them to the clipboard, then on a new line below, right-click and look for the Transpose option under Paste Special. Once that’s done, feel free to delete the original top row, or clear the contents of the first cell as we might come back to that row to add column names later.

There is some other cleaning up to do with this text, though; the Text-to-Columns function chopped everything at the “;” but there’s a space which follows the semicolon, so all the Display Names after the first one have a leading space. We could repeat the Text-to-Column feature on the selection again, but use a Space as delimiter now – unfortunately that would mangle the display names into multiple columns, and if we had a smattering of users with middle names or 3 or 4 part names common in many countries, it could make things look even worse.

Using the leading “<” of the email address as the delimiter is probably simplest, as it will separate the name(s) and email addresses out, though it does still give us a few tidying-up challenges, as there are spaces we don’t want and a trailing “>” at the end of every email address.

In cases like this, it’s easier to use a formula to clean things up – the Trim function being a good place to start; it removes both trailing and leading spaces in string, so the name can be fixed up into a new column.

Since we know the email address has one errant character – that trailing “>” – left behind from the earlier text-to-column operation, there are a variety of ways to strip it off. There’s the =LEFT() function, which keeps the left-most (n) characters of a string – so by combing the LEN function and knocking off a single character, we can chop the final character off.

After all this palaver, you might be thinking that some of this chopping around and formulaic string-handling can get a bit confusing as you start to nest operations within each other. Luckily, the Excel team has released some powerful new text-handling functions to try to simplify things a little:

TEXTBEFORE – Returns text that’s before delimiting characters
TEXTAFTER – Returns text that’s after delimiting character
TEXTSPLIT – Splits text into rows or columns using delimiters

So, using TEXTSPLIT on our original pasted text from the email, using “<” as the delimiter for the columns and “>; “ as the marker for the end of each row, gives us a near perfect solution – the only clanger being the trailing “>” on the last address.

You could use another formula to find and strip out any left-over characters like that, or just manually delete the last “>” off the original line you pasted in.

You decide.

625 – Journaling now and then

Posted on April 8, 2022 by ewand

Memoirs and autobiographies are the top selling non-fiction books for good reason, as people like to recall past events through the words and thoughts of someone who was there, in the room or even in the driving seat. World leaders who write their tell-all book on what happened 20+ years ago, better have great memories or perhaps a trove of notes and diary entries from the time. If they are fans of journaling, they would have of-the-moment musings, written down to help clear their minds at the time – on committing thoughts to her diary, Anne Frank wrote, “I can shake off everything as I write; my sorrows disappear, my courage is reborn.”

Turning to technology and looking back to relatively near-term history brings up all kinds of product that was ahead of its time or was ultimately overtaken by other developments that nobody saw coming. Sometimes, the perfect blend of genius, timing, execution and luck combines and creates a durable and wildly successful category – like the Smartphone and the plethora of services and apps that were created.

Inversely, one of those tech innovations that was just a bit ahead of its time was the Tablet PC; a fully-functional Windows PC that was blessed with a pen and touch screen so you could take notes by hand just like on paper, yet by flipping it around it could be used to run Office apps and all the other stuff you’d need a PC for, 20 years ago.

In hindsight, the idea of the Tablet PC was 10-15 years ahead of the technology that was needed to really make it work – the pen and screen digitizer were a bit too low-res; the processing power and memory was not up to the mark of providing the kind of user experience that the vision hoped for. The battery life was too poor while the whole thing was too heavy. Nowadays, with devices like the Surface Go and the iPad Pro, the reality is much closer – even if the dream of writing meeting notes by hand has been made somewhat obsolete by transcription and the fact that fewer people use a pen to write any more.

One new app that was built for the Tablet PC to take advantage of its pen, was Windows Journal, a relatively simple yet effective note-taking app, with surprisingly good handwriting recognition built in.

To read more from someone who was in the room – figuratively and, at times, literally – around the time of Tablet PC, the Journal software and the Office app originally called Scribbler which went on to become OneNote, check out Steven Sinofsky’s Hardcore Software post. It’s a fairly long but fascinating read.

Using pen and paper for taking meeting notes might be less popular now, but many of us will still jot down reminders or lists on Post-it notes, perhaps doodling on paper to help creativity and flow. If you have a pen-capable computer now, the newly released Microsoft Journal app is worth a look.

Billed as an app for digital ink enthusiasts, this new Journal presents a modern take on the original Windows Journal idea – an infinitely scrollable canvas for jotting down anything, though with AI capabilities in the app providing quiet yet powerful functionality. Journal started as a research project (from the “Garage”), but has now graduated into a fully-fledged, supported app. Read more about it here.

Maybe time to take a leaf from erstwhile storyteller Steve Clayton’s Friday Thing, and spend a few minutes every day handwriting a journal. Now where did I put that pen?

624 – Present in Teams, like a Boss

Posted on April 1, 2022 (Updated April 1, 2022) by ewand

Even after 2 years of mostly enforced remote meetings, it’s still amazing how many people have yet to master some of the basics of online meetings – like management of the mute button and general audio interference, positioning of screen/camera so you’re not looking up their nose or side of their face, professing to having bandwidth issues as the reason for not enabling video, and many more. One “room for improvement” function is that of presenting PowerPoint slides and not looking like an idiot.

Firstly, have a practice with Teams if you’re not sure how things are going to work out – just go to the Calendar tile and you’ll see a Meet now option in the top right; that creates a new instant meeting in which you can play.

Don’t share your screen to present slides in PowerPoint (unless you really insist). Instead, save your PowerPoint to OneDrive for Business or SharePoint, and you’ll see a Present in Teams button in the top right, or a larger button on the Slide Show tab.

Choosing this opens up a Presenter View akin to the one in PowerPoint, which is the default if you have multiple monitors and you start a Slide Show. This view lets you see Speaker Notes, jump quickly to specific slides rather than paging through them, and be more interactive with the meeting than you could ever be if you were simply sharing a screen showing a PowerPoint slide on your computer.

Perhaps the most useful aspect of this mode in Teams is that you can still show the Chat or People pane to the side of the window – allowing you to keep an eye on attendees who might have their hands raised, or who ask questions in the meeting chat.

There are some other controls of note – the eye icon lets you decide if attendees can flick through your slides or whether you want to lock them to seeing only the slide you’re currently presenting. Useful if you have a Big Reveal coming at the end.

Next to that icon, there are some others which define the presenter mode – Content Only on the left, shows just the slide you want. Next to that is Standout, which takes your video and overlays it onto the slide rather than having it appear as one of the surrounding gallery of other attendees. And next to that is a new preview PowerPoint feature called Cameo, which integrates with the Teams Client.

A downside of the Standout mode is that you don’t get to control where your image goes on screen, or how big it is – so you might well obliterate some part of the content you’re presenting. This new feature gives you a way to solve that.

In PowerPoint, go to the Insert tab and on each slide add a Cameo (or a Camera as the object it creates is described in some controls), then place and size it as you want.

If you select the new object, the Camera tab will give you more customization options.

Use the Camera Styles gallery to pick from a shape and border/shadow combination, though the Camera Shape menu offers other variants to enhance your impact.

You will need to add a Cameo to every slide you want to show up on – potentially useful if you want to only appear for intros and Q&A but perhaps leave the content on its own for other parts.

Since each slide has its own Camera object, they can be of different shapes and you can even use the groovy Morph animation effect to transition too.

While in Presenter view, try using a “laser pointer” to temporarily show traces around something on your slide, with mouse or Surface pen to control it. There is a pen or highlighter to make more durable Ink markups, and if you double-click/tap each icon, you can set options like size, colour, adding arrow tips etc.

One downside of the Presenter View is that it shrinks the content on your own screen to the point of possibly making it difficult to read, especially if you’re showing the People or Chat pane as well – in fact, the content is only about 20% of your screen real estate.

Using Pop Out might help if you have a larger second screen connected, though chances are you’ll be using the camera on a laptop so ideally want to be looking at that display.

Since nobody really uses Speaker Notes anyway, you could try Hide presenter view, which means you’ll lose the slide thumbnails and speaker notes, but still keep the other controls. Go to the View control on the top left of the window and choose Full Screen to increase it even more.

For more details on using the new Cameo feature, see here – it is in preview which is rolling out through Office Insiders first so you may not see it right away. If you are presenting using simple app or desktop sharing rather than the PowerPoint Live model described above, there are some other options in how you appear alongside your content.

As well as launching the PowerPoint Live sharing from within PPT itself, you can choose to share recent presentations while in Teams – just scroll down past the various “share screen / app” options and you’ll see more. This topic was covered previously on ToW #576.

623 – What’s .new pussycat?

Posted on March 25, 2022 by ewand

Many products evolve due to exposure to their competitors – adopting and refining the best features, and sometimes that evolution even starts to overtake the original. Many traditional desktop applications moved to online variants or were supplanted by newer concepts, such as shifting to mobile apps. Experiences that were clunky – like banking – moved to sometimes lower-functionality but more convenient apps, just as consumers adopted mobile payments and contactless cards.

Having blazed a trail with email in Hotmail and later Outlook Web Access, in 2010 Microsoft launched the first version of the Office web applications, meaning you could run lightweight Word, Excel and PowerPoint in your browser, as a companion or even as an alternative to the full-fat desktop versions.

A few years earlier, Google Docs released as an online word processor (and later, other types of productivity apps, rebranding as G Suite and now Google Workspace). There are pros and cons of the browser-only experience; you tend to sacrifice some functionality compared to the desktop applications in favour of ubiquitous availability, though web clients can be updated more easily and sometimes new features appear there first – as ToW #605 covered, with snoozing email.

Check out What’s new in Excel for the web or look for the summary covering Visio, Forms, Words and more, here.

Not sure about living in a browser? Modern-living afficionados can get by, using only web apps like Outlook, OneNote, To-Do and more.

If you like being browser based rather than desktop bound, you could start a new document from the address bar by simply entering word.new, excel.new or powerpoint.new. Others include docx.new, ppt.new, teams.new, sway.new …

You could add such links to your browser favourites; therefore, a new doc is but a single click away. There are many more .new shortcuts – Google’s in-house domain registry launched the service a few years ago, so not unsurprisingly, Mountain View hoovered up a lot of the relevant ones if you’re of a Googly persuasion. See docs.new, sheets.new or slides.new, mail.new …

618 – Listing

Posted on February 18, 2022 by ewand

Lists form a big part of lots of peoples’ lives – whether it’s a to-do list for productivity afficionados, shopping lists for remembering the essentials or compiling top-five lists of favourite things, just, well, because.

A while back, Microsoft released a new app for Microsoft 365 users called Lists, which was essentially a front-end to SharePoint, itself a staple of the Office 365/Microsoft 365 offering since the beginning, and providing much more functionality than simply a place to stuff documents. The original SharePoint Portal Server 2001 (codenamed “Tahoe”) is nearly old enough to buy itself a beer in its homeland, and relatively advanced logic and custom data validation & handling has been a major part of its appeal for a lot of that time.

Lists brings a lot of that functionality to the fore, while being extensible through Power Apps and integrated into Teams. There are mobile Lists apps available and in beta too.

Recently, the Lists experience was made available – in preview – for non-M365 users who could sign in with their Microsoft Account. A “lightweight” version of the app, it’s still pretty functional and pitched at individuals, families or small businesses who need to keep lists of things.

Taking a slightly different tack, the To Do application is a good way of making other sorts of lists – that could be Tasks or flagged emails as well as simple tick-lists to mark off what needs to be done. In something of an overlap with Lists, To Do can share its lists with other people – think of To Do as primarily for personal use that you might share, whereas Lists is for managing shared endeavours first and foremost.

If you’re a user of both Amazon’s Alexa services and Microsoft To Do, you might want to integrate them together; using the Tasks In The Hand skill. Once enabled and correctly configured, you can use Alexa to manage that service’s built-in To-Do and Shopping lists, and these are then synchronized to the Microsoft To Do app.

You can rename the lists which are subsequently created in To Do and which sync with Alexa, though you can’t yet manage additional ones. You could simply use the Alexa app to manage the lists rather than synching them with To Do, but setting up synch gives you more flexibility – To Do integrates with other software and services, like being able to show lists in the Microsoft Launcher app on an Android phone.

605 – Snooze la differénce

Posted on November 5, 2021 by ewand

Microsoft has always been good at having several ways of doing the same thing. Internal competition was encouraged with the idea that if several teams built solutions for the same problem, it would spur them all on and the best would win out. The Best Laid Plans don’t always work, and sometimes politics and machination gets in the way.

One modern incarnation of the multiple-ways principle is electronic mail; despite many attempts to replace email with other means of messaging, persistent chat etc, it’s still a huge deal (especially in business) and it’s still growing.

In the days when companies ran their own IT on-premises, there was Exchange, and the companion mail client Outlook arrived shortly after. Web-based consumer services like Hotmail, Yahoo! and Gmail changed the expectations of many users. Home and work email services have been getting closer in form and function since.

Microsoft’s current email clients are quite diverged: you can use the full-fat Outlook application to connect to your business email as well as your private ~~Hotmail~~ ~~MSN~~ ~~Live~~ Outlook.com / Gmail etc account. Then there’s the Windows Mail app, which is linked to the Outlook mobile apps so much so, that you can launch the Mail app by pressing WindowsKey+R then entering outlookmail: to start it (see more here).

The Mail app is pretty good – it can connect to a variety of sources including Office 365, so while it might not be an ideal primary business email application, it can be a good way of connecting to multiple personal email services.

One feature which appeared in different ways across multiple services and apps is the idea of Snoozing your email; initially pioneered by Gmail, others followed suit. It’s a different concept to flags and reminders, rather if you select an email and say snooze until 10am tomorrow, it will literally disappear from your inbox and it would reappear at the top of the pile the following morning.

Well, that’s how it works on some combinations. In the browser versions of both Hotmail / Outlook.com and Exchange/Office365, it works as you’d expect – you Snooze an email and it is actually moved into the Scheduled email folder (and you’ll see when it is later due to reappear in Inbox if you look in there). At the elected time, it shows up again on at the top of the mailbox. Let’s compare to some other Microsoft clients & services…

Outlook client and Office 365 – there is no snooze feature. Sorry. Just be more organised. If you snooze an email from another client, it will disappear from Inbox, but when it reappears, it’ll be in the same place as it was before – eg. if you Snooze a 9am email from the web app until 1pm, it will move into the Scheduled folder – but when it moves back into the Inbox, the Outlook and Windows Mail clients will show it down at 9am again so you might as well flag it and be done.

Windows Mail client with Outlook.com or Office 365 – Zip. Too bad.

Mobile Outlook and Web clients on Outlook.com or Office 365– Mail disappears and shows up again at the allotted time, right at the top of the mailbox. In the web clients, you’ll see the time stamp of the message as if it has literally just arrived; in the mobile version, though the message is ordered correctly (eg a 9am snooze to reappear at 1pm will show up between 12:30 and 1:15 mails), the displayed time is correct but a little clock icon is shown alongside. Clever.

At some point, there is a plan to deliver a single, unified, email client. An Ignite 2020 session talked about the roadmap and further commentary speculated that the One Outlook client may be coming, but isn’t going to be with us for some time yet.

In the meantime, maybe now’s the time to try the switch full time to Outlook Web App? You can even install it like a regular app, pin it to Start and so on.

602 – re-drawing Whiteboard

Posted on October 22, 2021 by ewand

One thing we all miss about having physical team meetings, is the delight of trying to read an enthusiastic participant’s attempt at getting their thought process across by scribbling on a whiteboard. Often with pens that have too little ink left to be legible. Charts with arrows always point up and left and bullet-point, capitalized text that may just be readable, but can anyone remember what it meant by the end of the meeting? At least you can take a picture to decipher later.

Fortunately, there are digital equivalences – you could be in a Teams meeting and co-authoring a document, where multiple people are editing at the same time and marking up comments. You could be watching someone share their 4K screen so they can walk through only a few dozen PowerPoint slides, or you might even have had a play with the shared Whiteboard app that’s been around and been part of Teams for a while now.

Well, the Whiteboard has had some pretty major updates recently.

The whole UI has been given an overhaul in line with the latest colourful design ethos, and there are lots of neat new features like the automatic shape recognition for mouse-driven drawing. Hold the Shift key down while you’re drawing with a mouse pointer or a Surface pen, and it’ll straighten lines for you.

It’s available in a variety of guises; there’s a web UI (app.whiteboard.microsoft.com) and it shows up in the menu on the top left of Office 365 web applications, such as subscribers would find by going to office.com and signing in with your ID. It’s on iOS and Android, though updates may flow through at different rates to other platforms.

Of course, it’s as a Microsoft Store app too; if you’re already a Windows 11 user, you may want to check out the new Store and look for the Library icon on the lower left, showing you what you’ve installed previously and also which apps have been most recently updated (and am Update button to kick off that process). Sadly, looking at an app’s page in the Store (still) doesn’t tell you what the current version is or when it was last updated.

To start Whiteboard in a Teams meeting, just go to the Share menu and look for the Whiteboard option, just under the part where you’d share your whole screen or individual window.

You can pin whiteboards to Teams channels or chats too; just add a Tab, select Whiteboard from the app list, and the content will persist within that context rather than a point-in-time meeting.

599 – Time for a short survey?

Posted on October 3, 2021 by ewand

There was a time when visiting a website gave you a 1/10 chance of being offered a user survey, asking why you’re visiting today and how you feel about the company. Presumably, response rates were low enough that such surveys are largely replaced with annoying cookies and tracker software so the company can see what you’re doing without needing to ask you why.

Surveys in email can be a lot more useful, though, when trying to garner feedback about a particular topic. Outlook has had Voting Buttons since it first appeared in Office 97. They provide a simple way of asking a single question and getting recipients of the email to respond so that the sender can see what the votes cast are. You can take the defaults or add your own custom options, separated by semicolons.

Recipients get prompted in Outlook and can vote with a single click, rather than having to type a response, and the sender can see a Tracking tab on the message in their Sent Items folder to get the results.

One downside of voting buttons, though, is that they only work in Outlook – there’s no Web App or mobile support, so it does restrict the usability somewhat. Great news, though – a more modern approach is available; not only does it work using the Outlook mobile apps and the browser but it’s a bit more in-your-face for most Outlook users too, with a simple and quick way of responding.

The Poll feature in Outlook appeared in March 2020 but may have been easy to miss, given all the other stuff that was happening then. It’s accessible from the Insert menu on a new message, or if you look at the bottom of the Use Voting Buttons drop-down menu, it’s been added there too.

Clicking on the icon gives you a single question with two or more options; it’s powered by Microsoft Forms, but there’s no fancy branching or data validation – it’s a straight “choose one of these short text responses” feature and all the better for it.

If using Outlook (desktop or mobile) or OWA, when a recipient in your organization receives an email with a Poll included, it’s shown right at the top and is super simple to reply to. If the recipient is using a mail client that doesn’t understand the Poll, there’s also a link to the web-based version too.

Since it’s delivered as part of a Microsoft 365 / Office 365 subscription, it’s a little less slick when dealing with users outside of the organization / tenant (the inline previews don’t show up, so outsiders will need to click the link and use the web UI, and will need to type their email address into the response too), so think of it as a friendly and visible way of collecting simple internal votes.

597 – Pivot table filtering

Posted on September 15, 2021 by ewand

Pivot Tables are a somewhat esoteric spreadsheet feature which everyday Excel users probably shy away from, but for the enlightened can help to turn potentially large tables of raw data into summarised views that can be easily rearranged.

Excel makes it easy to take a static table with rows of data arranged into columns that correspond to measures or fields – like sales of a product, or assignments of a relationship such as an export from a CRM system – and then represent the inherent hierarchy in a super-powerful fashion.

If you haven’t really used Pivot Tables, the simplest way to get your head around what they can do is to try messing around with some real data, or use some relatively simple sample data to unearth the power within. Free sample data at Contextures is a good place to start – in fact, here’s one sample that has been turned into a Pivot table to get started with (just download it somewhere, open the Zip file and then open the example XLSX file within).

The sample data is sales of foods over a period of time; look at the FoodSales tab to see the raw data. The pivot table quickly lets you summarise by region, or category, or whatever fields are defined.

By showing the PivotTable field list (right-click on the table and you’ll see either show or hide field list at the bottom of the menu), you can easily drag things around to change the grouping order.

It’s easier to just try it out than to try and explain, though there are plenty of tutorials around – from overviews to walkthrough videos or all kinds of more advanced stuff. And if you end up making a mess of the table, there’s also CTRL+Z to undo…

If you’re dealing with lots of fields that would go in the Rows section then you might find it easier to use the Classic layout by right-clicking on the table and choosing Pivot Table options, though you might want to remove the auto-totalling for most of the fields (right-click on a field and choose Subtotal…) otherwise the display gets a bit unwieldy.

When handling very large volumes of data, filtering using the drop-downs to the right side of field names is a great way of showing only the specifics you’re interested in, though sometimes it’s easy to forget there’s a filter in force and think that the data you’re working with is not showing the full picture. To reset all the filters back to the start, just go to the Data tab and Clear the filters from there.

If you want to filter on a specific value – eg. Let’s say you have a list of customer account names and you want to show only the customers associated to a single account manager (regardless of geography, industry etc), you could click the filter on the account manager field and find the person’s name within.

In our foodstuff example, we could have a different layout of fields and want to filter on City – but a quicker way to doing so is to just right-click on the name you want to focus on, and choose Filter > Keep Only Selected Items and it will quickly set the filter to just that name.

595 – Emoji reboot

Posted on August 27, 2021 by ewand

Emojis can trace their roots back to the first 🙂 from September 1982. Originally knows as emoticons or simply smileys, many of us have adopted these icons like a form of punctuation, especially in social media / Yammer / Teams type comments. This topic was last visited 4 years ago in ToW 391.

Emojis are mostly agreed and defined by the Unicode Consortium, which controls the Universal Coded Character Set, adopted by many systems to maintain compatibility between each other. When a user sends a symbol in a text message, the phone of its recipient needs to know which character was being sent or confusion may occur. Interpreting what the actual emoji symbol means is still down to the end user, and there are many pitfalls to avoid.

Once both sender and recipient of a message or comment agree which emoji to display, the application or platform they’re each using still has to decide what it will look like, and sometimes the iconography – and therefore the subtext – will have changed over time; see the Pistol Emoji (emojipedia.org) as just one example.

Microsoft decided to adopt a “flat” emoji look in the Windows 10 timeframe, but that is starting to change again with the upcoming release of Windows 11 and the evolution of Microsoft 365 – as Art Director and “Emojiologist” Claire Anderson previewed, we’re going 3D and Fluent, due late this year. Oh, one more thing…

ToW reader Paul Robinson draws attention to the shortcut way of inserting emojis in Windows – it’s been a feature for a while now – just press WindowsKey + . and it will allow you to insert emojis into pretty much anywhere that accepts text.

The UI for the emoji panel is changing in Windows 11 too, with GIFs and other types of symbol being included and the whole thing is easier to search. A useful tooltip shows you what the symbol represents, though as said before, be careful with the potential interpretation of some of them. Peachy.

In Windows 10, the same keystroke brings up a simpler yet slightly more confusing UI. Both old and new (under the Symbols grouping) provide a neat way of finding and inserting other special characters; arguably quicker than fishing about in the Office menu, and certainly better than faffing around with typing in ANSI codes.

Paul likes to start Teams channel names with an emoji, and if you want to illustrate one difference between old world and new, try using them in email subject lines and see just how they appear in Outlook versus Outlook Web App…