Tracy van der Schyff

#Office365Challenge The next couple of posts will be all about the various functions available in Excel to help you clean data you’ve received from other sources etc. Today we’ll take a look at the Left, Mid and Right Function which is used to cut specific length text from a string.

Left returns the specified number of characters for the start of a text string:

How to use Left: The formula is =Left(Text;Num_chars). This will return a specific number of characters. In this example my ’employee number’ which is always 10 characters:

Right returns the specified number of characters from the end of the text string:

How to use Right: The formula is =Right(Text;Num_chars). This will return a specific number of characters. In this example my ‘branch’ which is always 3 characters:

Mid returns the specified number of characters from the middle of the text string, based on a specified starting point. In this example, the text before it needs to be constant (same length).

How to use Mid: The formula is =Mid(Text;Start_Num;Num_chars). This will return a specific number of characters from a specified starting point in the string. In this example my ‘branch’ which is always 3 characters, after my Employee Number which is always 10 characters. So the starting point is 12 characters (employee number 10 + 1 space = 12 is first character):

Note:  Very important to make sure you know the separator to use in Excel, refer to a previous blog about this:  Around the Office in 365 Days: Day 44 – Excel / SharePoint Formulas using Commas or Semicolons. Excel will give you a preview of what the formula will look like – this is where you can see it whether it uses a semicolon or comma.

Tomorrow we’ll take a look at using Left, Mid and Right when you don’t have a specified length of characters.

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.