An Intro to Basic String Calculations: Left, Mid and Right functions

by Ross Easton

As someone who had never encountered formulas in any capacity before beginning to use Tableau, I often find myself encountering a problem and thinking ‘I bet there is a really simple way of solving this’. But, without being told, sometimes it can be difficult to know exactly what formulas exist even if there may be one out there that perfectly and very simply solves the issue you are encountering.

One such moment happened a few days ago when, whilst being taught Tableau Prep, I was faced with a string that looking something like this in each row: W118-684. I knew I only wanted the first character from each row of data and I sat there thinking ‘there must be a simple way of taking just the first character and leaving the rest’. And there naturally was a way – namely a Left string calculation. So I thought I’d write this blog post to save people from pondering the same question.

I have created an example string field as seen here:

My example string field of inconveniently merged names

For the purposes of this exercise we are going to try to separate out each name into individual columns within Tableau.

First of all we are going to separate ‘Chris’ from the merged field. For this we will use a Left function.

The target of our Left function

Create a calculated field, and type Left. Within the brackets the Left function wants to know the target string, and then the number of characters it should count starting from the left before stopping. The word ‘Chris’ has 5 letters in it so we type 5, but the basic format is:

Left([the name of your target field],the number of characters you wish to include)

Our completed Left string calc

This then gives us this result:

And as you can see we now have a separate field populated only by the ‘Chris’ that we wanted.

Next up we are going to use a Right string calculation to separate out the word ‘Clare’ from our merged field.

The target of our Right string calc

The Right calculation operates much the same as the Left calculation, but simply counts from the right rather than from the left.

So once again we should create a calculated field, this time writing Right. The format is essentially the same as with the Left:

Right([target field],number of characters the calculation should include)

So once again we target the Merged Names field, and ‘Clare’ has 5 characters so we ask the calculation to count 5.

Our completed Right string calc

The result we get is this:

The output of our Right string calc

And so we have successfully separated the ‘Clare”s out into their own field.

Finally we will use a Mid function to pull ‘Ross’ out from the middle of the merged string.

The target of our Mid function

The Mid string calculation is similar once again to the Left and Right calculations we have already used, but this time requires slightly more information to operate properly. The basic format of it is this:

Mid([target string],the character at which the calculation should start counting, how many characters it should count for)

As written above as well as needing to know a start point (tip: count the number of characters from the left as that is what the calculation will do) the Mid calculation also needs to know how many characters it should continue before stopping. So for our example we want it to start on the ‘R’ (the 6th character – so we write 6), and then ‘Ross’ has 4 letters (so we write 4).

Our completed Mid calc

And so we get this result:

Our Mid extract

This is only the beginning of what you can do with string calculations, but hopefully it will help some of you to start to understand what they do, and maybe even get you out of a few jams.

© 2022 The Information Lab Ltd. All rights reserved.