This week’s YouTube video is based on a real-life scenario. Last week my other half Elaine Giles, an expert in Affinity Publisher, handed me an Excel file with a problem to solve.

She is to spreadsheets to what I am to design. Actually I tell a lie. She’s not bad with Excel, it’s just she’s hardly touched it since she stopped teaching it about 20 years ago.

In this spreadsheet column A contained a list of almost 30 Affinity Publisher tutorials that she had delivered as YouTube live streams, together with the YouTube URL of each one. Here’s what the first 3 rows look like…

Each tutorial title and URL pair was separated by a colon-and-a-space and what she wanted was split the single-cell string so that the tutorial title was placed in column B and the URL in column C.

In 2022 Microsoft added 2 new functions to Excel. TEXTBEFORE and TEXTAFTER. They do exactly what they sound like – extract text before and after a character or string of characters.

So for example =TEXTBEFORE(A2, “: http”) returns all the text from A2 that is before, i.e. to the left of “: http”.

But these 2 functions are only available in Excel 365, the subscription version of Excel. There are millions of users still using a non-subscription version of Excel such as 2019, 2016 or even earlier.

To solve this problem, users of these versions will need to know about the LEFT, RIGHT and LEN functions. Note: You can still use these functions in Excel 365 but to solve this particular problem why make life more difficult – use TEXTBEFORE and TEXTAFTER.

Then there’s the emoji character count gotcha and the space-at-the-end gotcha.

Want to know more? Check out the video…